MySQL 5.7无JSON_TABLE如何实现JSON字段转结构化表格输出

MySQL 5.7 没有JSON_TABLE的对等内置函数，但可以通过JSON提取函数+动态SQL实现需求，分两种场景处理：

场景1：提前明确JSON内的所有键名 #

这是最简便的场景，直接使用MySQL 5.7支持的JSON_EXTRACT函数（或其简写语法->>）逐字段提取即可：

SELECT 
  id,
  CAST(jdoc->>'$.CS' AS UNSIGNED) AS `CS`,
  CAST(jdoc->>'$.Physics' AS UNSIGNED) AS `Physics`,
  CAST(jdoc->>'$.Chemistry' AS UNSIGNED) AS `Chemistry`
FROM test_table;

说明：->>是JSON_UNQUOTE(JSON_EXTRACT())的语法糖，会自动去掉提取结果的引号，外层加CAST转换为数值类型即可匹配示例输出要求。

场景2：JSON键名动态不固定 #

如果无法提前获知所有键名，可以通过动态SQL拼接的方式实现，步骤为：先提取所有JSON中的唯一键名，再自动拼接查询语句执行。
示例代码如下：

-- 1. 生成包含所有键的查询字段片段
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'CAST(jdoc->>"$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(jdoc), CONCAT('$[', n, ']'))), '" AS UNSIGNED) AS `', 
    JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(jdoc), CONCAT('$[', n, ']'))), '`'
  )
) INTO @sql
FROM test_table
-- 此处数字序列长度需要覆盖单条JSON最多的键数量，最多5个键就写到4即可（下标从0开始）
JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) nums
WHERE n < JSON_LENGTH(JSON_KEYS(jdoc));

-- 2. 拼接完整查询语句
SET @sql = CONCAT('SELECT id, ', @sql, ' FROM test_table');

-- 3. 执行动态查询
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项： #

• 上述代码中的nums临时数字序列长度需要根据业务场景调整，比如单条JSON最多有10个键，就追加UNION ALL SELECT 9把序列补到9即可
• 代码中对键名用反引号包裹，同时做了去重处理，兼容不同行JSON键不一致的场景，缺失的键对应行的值会返回NULL
• 如果不需要转换为数值类型，去掉CAST相关的代码即可

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