如何从MySQL的JSON字符串字段中提取唯一嵌套变量名？

嘿，这个场景我之前帮同事处理过——面对这种存了2500+无序变量的JSON字段，手动写json_extract完全是噩梦，给你几个实用的解决思路：

首先得提一下你示例里的JSON有两个不符合MySQL标准的问题：

• 键名没有双引号（比如var1str:而不是"var1str":），MySQL的JSON函数只认带双引号的键；
• 小数用逗号做分隔符（比如0,01），标准JSON要求用点.。

这两个问题不解决，所有JSON解析函数都会报错，所以第一步要先做格式转换，用REPLACE就能搞定：

-- 先补全键的双引号，再替换小数分隔符得到合法JSON
REPLACE(REPLACE(DATA, '{', '{"'), ':', '":')

如果你的MySQL版本是8.0及以上，JSON_TABLE绝对是最优解——它能直接把无序的JSON对象转成关系型表结构，不管变量顺序如何都能正确映射。

针对你给出的样本数据，完整查询是这样的：

SELECT 
  t.ID,
  jt.*
FROM your_table t
CROSS JOIN JSON_TABLE(
  -- 把原始DATA转换成合法JSON
  REPLACE(REPLACE(t.DATA, '{', '{"'), ':', '":') AS valid_json,
  '$' COLUMNS (
    var1str VARCHAR(255) PATH '$.var1str',
    var2double DOUBLE PATH '$.var2double',
    var3integer INT PATH '$.var3integer',
    var4str VARCHAR(255) PATH '$.var4str'
    -- 这里可以继续添加其他字段，但2500+的话手动写不现实，看方案2
  )
) jt;

手动写2500个字段根本不现实，我们可以让MySQL自动提取所有JSON键，然后生成完整的查询语句。

步骤1：提取所有唯一的JSON键 #

先运行这个语句，把DATA字段里所有出现过的变量名都提取出来：

SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(json_keys, CONCAT('$[', idx, ']'))) AS key_name
FROM (
  SELECT 
    JSON_KEYS(REPLACE(REPLACE(DATA, '{', '{"'), ':', '":')) AS json_keys,
    idx
  FROM your_table
  -- 生成足够多的序号，这里生成到3000，覆盖2500+的变量量
  CROSS JOIN (
    SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    -- 可以继续添加UNION ALL SELECT ... 直到序号数量超过2500
  ) AS number_list
) t
WHERE idx < JSON_LENGTH(json_keys);

步骤2：自动生成完整查询 #

把上面的子查询嵌套，用GROUP_CONCAT拼接出所有字段的提取语句：

SELECT CONCAT(
  'SELECT ID, ',
  GROUP_CONCAT(
    CONCAT(
      'JSON_UNQUOTE(JSON_EXTRACT(REPLACE(REPLACE(DATA, ''{'', ''{"''), '':'', '":''), ''$.', key_name, '')) AS ', key_name
    ) SEPARATOR ', '
  ),
  ' FROM your_table;'
) AS dynamic_extract_query
FROM (
  -- 这里放步骤1的子查询
  SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(json_keys, CONCAT('$[', idx, ']'))) AS key_name
  FROM (
    SELECT 
      JSON_KEYS(REPLACE(REPLACE(DATA, '{', '{"'), ':', '":')) AS json_keys,
      idx
    FROM your_table
    CROSS JOIN (
      SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      -- 补充足够多的序号
    ) AS number_list
  ) t
  WHERE idx < JSON_LENGTH(json_keys)
) key_list;

运行这个语句后，会生成一个完整的SELECT语句，复制出来直接执行，就能一次性提取所有2500+个变量了。

• 性能优化：如果经常需要查询这些JSON里的字段，建议直接把JSON数据同步到一个关系型表中，或者给常用的JSON键创建JSON索引，避免每次查询都解析超大JSON；
• 版本兼容：如果你的MySQL是5.7版本，JSON_TABLE用不了，只能用方案2的动态生成json_extract的方法；
• 数据校验：转换格式后最好用JSON_VALID()函数验证一下，确保所有DATA都能被正确解析：SELECT ID, DATA FROM your_table WHERE JSON_VALID(REPLACE(REPLACE(DATA, '{', '{"'), ':', '":')) = 0;

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