mysql列转多行

初始化数据
执行初始化数据脚本。

create view v1 as 
select '张三' as name 
union
select '李四,张三' as name 
union
select '张三,王五,刘六' as name;

执行查询sql:select * from v1;，结果如下：

需求
需要统计所有的DISTINCT name.

思路

需要将带有,分隔的行的数据转换为多行。
然后使用mysql的 substring_index()方法，截取特定的长度的字符串。

1.统计每一行有多少个特定字符’,'

select v1.name ,(length(v1.name) - length(replace(v1.name,',',''))+1) as len	 from v1;	

2.将单行数据‘扩展’为‘len’行
如何扩展呢? 我们可以建一张辅助表，表的内容很简单，是一段连续的数值.

CREATE TABLE `mysql_help_auto_increment` (
  `id` tinyint(3) unsigned,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into mysql_help_auto_increment values(0);
insert into mysql_help_auto_increment values(1);
insert into mysql_help_auto_increment values(2);
insert into mysql_help_auto_increment values(3);
insert into mysql_help_auto_increment values(4);
insert into mysql_help_auto_increment values(5);
insert into mysql_help_auto_increment values(6);
insert into mysql_help_auto_increment values(7);
insert into mysql_help_auto_increment values(8);
insert into mysql_help_auto_increment values(9);

然后，步骤1的结果与这张辅助表左关联。

SELECT
	v1.NAME,
	b.id 
FROM
	v1
	LEFT JOIN mysql_help_auto_increment b ON b.id < ( length( v1.NAME ) - length( REPLACE ( v1.NAME, ',', '' ))+ 1 );

此时，我们就已经将单行数据成功为多行数据了。

3.substring_index(str,delim,count)

引用：https://www.cnblogs.com/mqxs/p/7380933.html

substring_index(str,delim,count)
      str:要处理的字符串
      delim:分隔符
      count:计数

• 如果count为是正数，那么就是从左往右数，第N个分隔符的左边的全部内容
• 如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容

最终结果：

SELECT
	v1.NAME,
	b.id ,
  substring_index(substring_index(v1.NAME,',',b.id+1),',',-1)   
FROM
	v1
	LEFT JOIN mysql_help_auto_increment b ON b.id < ( length( v1.NAME ) - length( REPLACE ( v1.NAME, ',', '' ))+ 1 );