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

相关文章