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导致出现如下场面：

相关文章