MySQL根据逗号将一行数据拆成多行数据

原始数据

 

 

 处理结果展示

 

 

 DDL

CREATE TABLE `company` (
`id` int(20) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`shareholder` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DML

INSERT INTO `company` VALUES ('1', '阿里巴巴', '马云'); 
INSERT INTO `company` VALUES ('2', '淘宝', '马云,孙正义');
INSERT INTO `company` VALUES ('2', '淘宝', '马云,孙正义,茅五');

三种方式，相同的原理

1.使用MySQL库中的自增序列表

SELECT
    a.id,
    a. NAME,
    substring_index(
        substring_index(
            a.shareholder,
            ',',
            b.help_topic_id + 1
        ),
        ',' ,- 1
    ) AS shareholder
FROM
    company a
JOIN mysql.help_topic b ON b.help_topic_id < (
    length(a.shareholder) - length(
        REPLACE (a.shareholder, ',', '')
    ) + 1
)

2.自建自增序列表

CREATE TABLE `addself` (
`id` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `addself` VALUES ('0'); 
INSERT INTO `addself` VALUES ('1');
INSERT INTO `addself` VALUES ('2');
INSERT INTO `addself` VALUES ('3');
INSERT INTO `addself` VALUES ('4');

SELECT
    a.id,
    a.NAME,
    substring_index(
        substring_index(
            a.shareholder,
            ',',
            b.id+ 1
        ),
        ',' ,- 1
    ) AS shareholder
FROM
    company a
JOIN addself b ON b.id< (
    length(a.shareholder) - length(
        REPLACE (a.shareholder, ',', '')
    ) + 1
)

3.以数据库里已有表，构建自增序列表

select a.ID,a.name,substring_index(substring_index(a.shareholder,',',b.id+1),',',-1) shareholder
from
company a
join
(SELECT (@ROW :=@Row + 1) as id FROM xh,(SELECT @Row:=-1) zz) b
on b.id < (length(a.shareholder) - length(replace(a.shareholder,',',''))+1);