MySQL 5.7中JSON_TABLE函数替代方案咨询（动态行转列）

现有行转列存储过程 #

我实现的行转列存储过程如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `pivot`(tablename VARCHAR(10000),
                        groupname VARCHAR(64),
                        groupname2 VARCHAR(64),
                        groupname3 varchar(64),
                        groupname4 varchar(64),
                        groupname5 varchar(64),
                        pivotname VARCHAR(64),
                        valuename  VARCHAR(64))
BEGIN
SELECT CONCAT('CREATE VIEW to_columnslist AS\n',
              'SELECT DISTINCT CONCAT(\'`\', `', pivotname,'`, \'` VARCHAR(255) path \\'$."\', ', pivotname,', \'"\\'\') line\n',
              'FROM (', tablename, ') a')
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT CONCAT(
'SELECT to_json.`', groupname,'`,`',groupname2,'`,`',groupname3,'`,`',groupname4,'`,`',groupname5,'`, parsed.*', '\n',
'FROM (SELECT `', groupname,'`,`',groupname2,'`,`',groupname3,'`,`',groupname4,'`,`',groupname5,'`, JSON_OBJECTAGG(`', pivotname,'`, `', valuename,'`) json_data', '\n',
'      FROM (', tablename,') a', '\n',
'      GROUP BY `', groupname,'`,`',groupname2,'`,`',groupname3,'`,`',groupname4,'`,`',groupname5,'`  ORDER BY `',groupname2,'` DESC) to_json', '\n',
'CROSS JOIN JSON_TABLE( json_data,', '\n',
'                       "$" COLUMNS ( ', 
GROUP_CONCAT(line SEPARATOR ',\n                                     '),
' ) ) parsed'
) sql_text
INTO @sql
FROM to_columnslist;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
DROP VIEW to_columnslist;
END

报错信息 #

因为必须使用MySQL 5.7而非8.0，调用时出现如下语法错误：

Get_QueryResultSet Error :
SP_GET_CODEDESC(?,?,?) com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near '( json_data,
                           "$" COLUMNS ( `Cash` VARCHAR(255) path '$."C' at line 11

问题与需求 #

问题根源是MySQL 5.7不支持JSON_TABLE函数。我曾尝试用硬编码的JSON_EXTRACT改写，但不仅报“Result consisted of more than one row”错误，还无法适配用户可动态增减价格桶的需求。

需要寻找MySQL 5.7兼容、支持动态数据的JSON_TABLE替代方案，且不能升级至MySQL 8.0。

替代方案：动态生成JSON_EXTRACT列 #

MySQL 5.7虽然没有JSON_TABLE，但可以通过动态拼接JSON_EXTRACT函数实现动态列提取，核心思路是：

1. 先获取所有需要转列的唯一pivotname值
2. 动态拼接每个列对应的JSON_EXTRACT(json_data, '$.xxx') AS xxx语句
3. 把这些语句整合到最终查询SQL中

修改后的存储过程如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `pivot`(tablename VARCHAR(10000),
                        groupname VARCHAR(64),
                        groupname2 VARCHAR(64),
                        groupname3 varchar(64),
                        groupname4 varchar(64),
                        groupname5 varchar(64),
                        pivotname VARCHAR(64),
                        valuename  VARCHAR(64))
BEGIN
    -- 第一步：获取所有需要转列的唯一pivot值，生成动态列表达式
    SELECT CONCAT('CREATE VIEW to_columnslist AS\n',
                  'SELECT DISTINCT CONCAT(\'JSON_EXTRACT(json_data, \'\'$."', ``, pivotname, '', '')\'') AS `', ``, pivotname, '', '')\'') line\n',
                  'FROM (', tablename, ') a')
    INTO @sql;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;

    -- 第二步：拼接最终查询SQL，将动态列整合进去
    SELECT CONCAT(
        'SELECT `', groupname, '`, `', groupname2, '`, `', groupname3, '`, `', groupname4, '`, `', groupname5, '`,\n',
        GROUP_CONCAT(line SEPARATOR ',\n'),
        '\nFROM (\n',
        '    SELECT `', groupname, '`, `', groupname2, '`, `', groupname3, '`, `', groupname4, '`, `', groupname5, '`,\n',
        '           JSON_OBJECTAGG(`', pivotname, '`, `', valuename, '`) AS json_data\n',
        '    FROM (', tablename, ') a\n',
        '    GROUP BY `', groupname, '`, `', groupname2, '`, `', groupname3, '`, `', groupname4, '`, `', groupname5, '`\n',
        '    ORDER BY `', groupname2, '` DESC\n',
        ') AS to_json'
    ) INTO @sql
    FROM to_columnslist;

    -- 执行动态SQL并清理临时对象
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    DROP VIEW to_columnslist;
END

方案说明 #

• 用GROUP_CONCAT动态生成每个pivot值对应的JSON_EXTRACT语句，替代JSON_TABLE的列解析功能
• 保留原存储过程的动态性，不管pivotname对应数值新增或减少，都会自动适配生成对应列
• 解决了硬编码JSON_EXTRACT无法动态适配的问题，同时避免了多返回行的错误

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