MySQL 5.7中解析JSON数组列数据的SQL实现方案

嘿，我之前刚好处理过MySQL 5.7下的JSON数组拆分需求，毕竟5.7没有8.x里的JSON_TABLE这种直接的函数，得用点小技巧来实现。先理清楚你的需求：把每条记录里RFIDs列的JSON数组拆成单独的RFID行，同时保留Product、Siz、Color这几个字段的对应值。

原始数据示例 #

期望输出示例 #

解决方案：用数字辅助表+JSON函数实现 #

因为MySQL 5.7没有JSON_TABLE，我们可以通过数字辅助表来遍历JSON数组的索引，然后提取对应位置的元素。

方法1：创建临时数字表（适合多次使用）

首先创建一个临时表，存储数组可能用到的索引值（数字数量要覆盖你的JSON数组最大长度，比如你的示例里是3个元素，所以0、1、2就够，多写几个也没关系）：

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (0), (1), (2), (3), (4);

然后执行主查询，关联这个数字表来拆分数组：

SELECT
    t.Product,
    t.Siz,
    t.Color,
    -- 提取对应索引的元素，并去掉前后的双引号
    TRIM(BOTH '"' FROM JSON_EXTRACT(t.RFIDs, CONCAT('$[', nums.n, ']'))) AS RFID
FROM
    your_table t -- 替换成你的实际表名
JOIN
    nums ON nums.n < JSON_LENGTH(t.RFIDs) -- 只取数组存在的索引
ORDER BY
    t.Product, t.Siz, t.Color, nums.n;

方法2：用UNION ALL生成临时数字序列（适合单次查询）

如果不想创建临时表，可以直接用UNION ALL生成需要的索引数字：

SELECT
    t.Product,
    t.Siz,
    t.Color,
    TRIM(BOTH '"' FROM JSON_EXTRACT(t.RFIDs, CONCAT('$[', nums.n, ']'))) AS RFID
FROM
    your_table t -- 替换成你的实际表名
JOIN
    (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) nums
        ON nums.n < JSON_LENGTH(t.RFIDs)
ORDER BY
    t.Product, t.Siz, t.Color, nums.n;

代码解释 #

• JSON_LENGTH(t.RFIDs)：获取每条记录中JSON数组的长度，确保我们只遍历数组实际存在的索引，避免提取到NULL值
• JSON_EXTRACT(t.RFIDs, CONCAT('$[', nums.n, ']'))：通过拼接索引字符串，提取数组中对应位置的元素
• TRIM(BOTH '"' ...)：因为JSON_EXTRACT提取出来的元素会带双引号，用这个函数去掉前后的引号，得到干净的RFID值

这个方案完全适配MySQL 5.7版本，测试你的示例数据可以得到你想要的输出结果。

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