如何在MySQL v5中提取字符串中所有productCode值并拼接为新列

MySQL 5.x没有原生JSON解析函数，只能通过字符串操作实现需求，以下是具体解决方案：

1. 创建临时数字辅助表 #

用于遍历JSON中productCode的出现次数，可根据数据量调整数字范围：

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

2. 提取并拼接productCode的查询语句 #

假设你的表名为your_table，存储JSON的字段名为content：

SELECT
    t.content,
    GROUP_CONCAT(
        NULLIF(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    SUBSTRING_INDEX(t.content, '"productCode":"', n.n + 1),
                    '"productCode":"', -1
                ),
                '"', 1
            ), ''
        )
        ORDER BY n.n
        SEPARATOR ','
    ) AS products
FROM your_table t
JOIN nums n
    ON n.n <= (LENGTH(t.content) - LENGTH(REPLACE(t.content, '"productCode":"', '')))
GROUP BY t.content;

原理说明 #

• LENGTH(t.content) - LENGTH(REPLACE(t.content, '"productCode":"', ''))：计算字符串中"productCode":"的出现次数，确定productCode的总数。
• 嵌套SUBSTRING_INDEX：逐层截取每个productCode对应的取值。
• NULLIF：过滤空值（如部分product无productCode的情况）。
• GROUP_CONCAT：将所有提取到的productCode用逗号拼接成字符串。

注意事项 #

• 该方法依赖JSON格式固定，productCode键名必须严格为"productCode":"（含引号、冒号格式），若存在空格、大小写差异需调整匹配字符串。
• 若JSON中productCode数量超过临时表数字范围，需扩展nums表的数字。

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