如何在MySQL中展开列表数据并生成对应ID的多行记录

现有如下数据表结构：

需求是将其中的JSON数字列表拆分成多行，关联原id，得到如下结果：

解决方案 #

方法一：MySQL 8.0+ 用JSON_TABLE（推荐） #

MySQL 8.0及以上版本支持JSON_TABLE函数，能直接把JSON数组转成关系表，语法简洁高效：

SELECT 
    t.id,
    j.num AS list_of_numbers
FROM 
    your_table_name t,
    JSON_TABLE(
        t.list_ofnumbers,
        '$[*]' COLUMNS (num INT PATH '$')
    ) j;

替换your_table_name为你的实际表名即可。$[*]表示遍历JSON数组的所有元素，num INT PATH '$'指定提取的元素类型为整数。

方法二：MySQL 5.x 用辅助表+字符串拆分 #

如果你的MySQL版本低于8.0，没有JSON_TABLE，可以用数字辅助表配合字符串函数实现：

1. 先创建一个数字辅助表，里面存足够多的连续数字（数量要大于你数组的最大长度）：

CREATE TABLE numbers (n INT PRIMARY KEY AUTO_INCREMENT);
-- 插入10行，按需增加数量
INSERT INTO numbers VALUES (),(),(),(),(),(),(),(),(),();

2. 执行拆分SQL：

SELECT 
    t.id,
    CAST(
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(
                REPLACE(REPLACE(t.list_ofnumbers, '[', ''), ']', ''),
                ', ', n.n
            ),
            ', ', -1
        ) AS UNSIGNED
    ) AS list_of_numbers
FROM 
    your_table_name t
JOIN 
    numbers n 
ON 
    n.n <= JSON_LENGTH(t.list_ofnumbers)
ORDER BY 
    t.id, n.n;

这里先用REPLACE去掉数组的方括号，把内容转成逗号分隔的字符串，再用SUBSTRING_INDEX逐个截取元素，JSON_LENGTH用来控制只截取数组实际存在的元素。

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