如何实现MySQL中SELECT *查询自动构建JSON_OBJECT的通用方案？

MySQL原生暂不支持SELECT JSON_OBJECT(*) FROM table这类直接将通配符查询结果转为JSON对象的语法，以下两种方案可实现无需手动指定全表列名，直接返回任意表全字段的JSON_OBJECT结果： #

方案1：一次性动态SQL #

适用临时查询场景，仅需替换表名变量即可使用：

-- 替换为你要查询的目标表名
SET @target_table = 'your_table_name';

-- 自动拼接列名生成查询SQL
SET @query_sql = CONCAT(
    'SELECT JSON_OBJECT(',
    GROUP_CONCAT('\'', COLUMN_NAME, '\', ', COLUMN_NAME ORDER BY ORDINAL_POSITION),
    ') AS row_json FROM ', @target_table
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @target_table;

-- 执行查询
PREPARE stmt FROM @query_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方案2：封装为通用存储过程 #

适用高频复用场景，一次封装后续可直接调用：

存储过程创建代码 #

DELIMITER //
CREATE PROCEDURE GetTableAllRowsAsJson(IN input_table_name VARCHAR(128))
BEGIN
    -- 校验表名是否存在，增加容错
    IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = input_table_name
    ) THEN
        -- 拼接生成查询SQL
        SET @sql = CONCAT(
            'SELECT JSON_OBJECT(',
            GROUP_CONCAT('\'', COLUMN_NAME, '\', ', COLUMN_NAME ORDER BY ORDINAL_POSITION),
            ') AS row_json FROM ', input_table_name
        )
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = input_table_name;
        
        -- 执行SQL
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '当前数据库下不存在指定表';
    END IF;
END //
DELIMITER ;

调用方法 #

-- 传入表名即可得到全字段JSON结果
CALL GetTableAllRowsAsJson('your_table_name');

注意事项 #

• 如需支持WHERE筛选、LIMIT分页等逻辑，可在存储过程中增加对应参数，拼接进生成的SQL语句即可
• 生产环境使用时需做好表名参数的校验，避免SQL注入风险

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