在某些业务上,为了使用或者开发方便,我们会使用一个字段来存储一个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导致出现如下场面: