mysql根据逗号将一行数据拆分成多行数据

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', '淘宝', '马云,孙正义');

• 三种方式，相同的原理

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);

   xh表是库里已有表(可以不是序列表)，行数必须大于分割字段的最大逗号数

• 小结

1. 序列表必须从0开始，行数与最长逗号有关，行数至少比最长逗号个数加1，可以建0~1000。
2. 为什么不用MySQL自带的自增序列表mysql.help_topic？因为好多公司的数据库是没有权限操作这些表的, 不能使用。

• 本文参考了：https://blog.csdn.net/WY18334783911/article/details/83379450  感谢！
  希望对大家有所帮助。