mysql 一行数据拆分多行

# 查找出被逗号分隔字段需要拆分的最大数量
select max((LENGTH(逗号分隔的字段)-LENGTH(REPLACE(逗号分隔的字段, ',', ''))+1)) from 处理表 where 条件;

# 创建一张临时表用于联合查询，方便把处理表单行记录分隔为多行
CREATE TEMPORARY TABLE incre_table (
    `id` int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);
insert into incre_table values (1);
insert into incre_table values (2);
insert into incre_table values (3);
insert into incre_table values (4);
insert into incre_table values (5);
insert into incre_table values (6);
insert into incre_table values (7);
insert into incre_table values (8);
insert into incre_table values (9);
insert into incre_table values (10);
# ... 大于 需要拆分的最大数量

# 关键在于连表查询 ON b.id <= 逗号分隔的数量
SELECT
    a.id,
    substring_index(
        substring_index(a.逗号分隔的字段, ',', b.id),
        ',', - 1
    )
FROM
    处理表 a
RIGHT JOIN incre_table b ON b.id <= (
    LENGTH(a.逗号分隔的字段) - LENGTH(REPLACE (a.逗号分隔的字段, ',', '')) + 1
)
WHERE
    a.条件;