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

第一处标红的作用:为了截取第几个课程

第二处标红的条件:计算分隔符即逗号的个数,防止因为join导致出现如下场面: