SingleStore MemSQL中Departments表多列排序查询报错及最优方案咨询

错误原因 #

SingleStore分布式模式下，无法在同一条查询语句中同时查询INFORMATION_SCHEMA系统表和用户业务表，这是架构层面的限制，所以你之前的语句会报错。

最优实现方案 #

因为不能在同一查询里关联系统表和业务表，我们可以分两步完成需求，或者用存储过程自动化执行：

方案1：手动拼接查询语句（临时查询用）

1. 先单独获取按列名升序排列的所有列名：

SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME ASC) AS sorted_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Departments'
  AND TABLE_SCHEMA = '你的数据库名称'; -- 替换成实际数据库名，防止同名表冲突

执行后会得到一个逗号分隔的列名字符串，比如Age,City,Name,...（按字母升序排列）。

2. 将得到的列名字符串替换到查询语句中执行：

SELECT Age, City, Name, ... -- 这里替换第一步得到的所有列名
FROM Departments
ORDER BY Name, Age, City ASC;

方案2：用存储过程自动化执行（重复查询用）

如果需要多次执行这个查询，可以写个存储过程自动拼接并执行语句：

DELIMITER //
CREATE PROCEDURE GetSortedDepartments()
BEGIN
    -- 定义变量存储排序后的列名字符串
    DECLARE sorted_cols VARCHAR(10000);
    -- 获取当前数据库下Departments表的升序列名
    SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME ASC) INTO sorted_cols
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Departments'
      AND TABLE_SCHEMA = DATABASE();
    
    -- 拼接查询语句
    SET @sql = CONCAT('SELECT ', sorted_cols, ' FROM Departments ORDER BY Name, Age, City ASC');
    -- 预处理并执行语句
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

调用存储过程即可得到结果：

CALL GetSortedDepartments();

注意事项 #

• 必须指定TABLE_SCHEMA，否则如果多个数据库下有同名的Departments表，会拿到错误的列名。
• 如果列数太多导致GROUP_CONCAT结果被截断，可以临时调整会话级别的长度限制：SET SESSION group_concat_max_len = 20000;（数值根据实际需要设置）

内容的提问来源于stack exchange，提问作者venkat