MySQL通过分隔符对字段拆分即一行转多行sql写法

在某些情况中,为了使用或者开发方便(偷懒),我们会使用一个字段来存储一个list数据,通过逗号、分号、顿号等分隔符区分。在java代码中,处理非常方便,将字段读取并分割成list即可,但是如果我们要使用sql直接查询该怎么做呢?

在mysql下,为了实现上述操作,我们可以使用substring_index函数搭配mysql.help_topic表来实现。

1、substring_index(str,delim,count)
str:需要处理的字符串
delim:分隔符
count:表示截取到第几个分隔符,正数为从开头向右截取,负数为从结尾向左截取。
举个例子:假如有个字符串为:语文、数学、英语。现需要将3门课程分别截取出来,写法如下:

select
substring_index('语文、数学、英语','、',0),
substring_index('语文、数学、英语','、',1),
substring_index('语文、数学、英语','、',-1),
substring_index(substring_index('语文、数学、英语','、',2),'、',-1);

注:因为数学位于中间,所以数学的截取需要2次substring_index的配合。

2、mysql.help_topic表
这个表是mysql自带的一个表,具体表的内容可以使用

select * from mysql.help_topic;

查看。这里我们只是使用到了他的help_topic_id字段,因为他的help_topic_id字段是从0开始自增且不间断的。
为了搭配1函数的使用,理论上只要有一张表的id是从0开始自增且不间断的就可以,考虑到mysql.help_topic表每个数据库都有,这里直接使用它。

进入开头所说的例子:

CREATE TABLE `t_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL COMMENT '学生姓名',
  `courses` varchar(50) NOT NULL COMMENT '课程名列表,逗号分隔',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='学生表';
INSERT INTO `test`.`t_student`(`id`, `name`, `courses`) VALUES (1, '小明', '语文、数学、英语');

建一个表并插入数据,其中courses字段表示这个学生所需要学习的课程,是一个list,使用顿号分隔。

原始数据:

需要查出数据:

sql写法:

SELECT
    t.id AS 'id',
    t.NAME AS '姓名',
    substring_index( substring_index( t.courses, '、', h.help_topic_id + 1 ), '、',- 1 ) AS '课程'
FROM t_student t JOIN mysql.help_topic AS h ON h.help_topic_id < ( char_length( t.courses ) - char_length( REPLACE ( t.courses, '、', '' ) ) + 1 )
;

解释:
substring_index( substring_index( t.courses, '、', h.help_topic_id + 1 ), '、',- 1 )用于决定截取第几个课程
( char_length( t.courses ) - char_length( REPLACE ( t.courses, '、', '' ) ) + 1 )用于计算分隔符即逗号的个数,防止因为join导致出现如下场面: