目录
前言
由于某些特殊需求,需要直接从数据库中取数据交给甲方自己处理,因此对一些特殊字符串进行处理,比如 1|2|3|4|5 ,代表五个层级id,但是现在需要将这一个字段拆成五个列展示,这时候就需要用到 mysql 的字符串分割函数了,示例 sql 主要用到了 case when 、length、substring_index,本文重点便是 substring_index
函数介绍
substring_index
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
即 字符串截取函数,例子:str = 1|2|3|4|5
substring_index(str,'|',1)=1
substring_index(str,'|',2)=1|2
也就是说,如果count是正数,那么就是从左往右数,第N个分隔符的左边的全部内容;相反,如果是负数,那么就是从右边开始数,第N个分隔符右边的所有内容,如 substring_index(str,'|',-1)=5
如果要获得中间的数据,只需要调用两边函数即可,即先获取左边全部内容,然后获取右边的第一个,如
substring_index(substring_index(str,'|',2),'|',-1)=2
length
length(str)
str:要处理的字符串
即 查看字符串的长度,注意一个汉字是算三个字符,一个数字或字母算一个字符
由于本需求需要列出所有层级数据,但层级并不是固定的,因此需要额外判断当前数据有几层,即判断有几个 | 符号,可以使用当前函数实现,即:
(LENGTH('1|2|3|4|5') - LENGTH(REPLACE ('1|2|3|4|5', '|', '')))=4
sql 举例
select path
, substring_index(path
, '|'
, 1) as first_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 1
then substring_index(substring_index(path
, '|'
, 2)
, '|'
, -1) end as second_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 2
then substring_index(substring_index(path
, '|'
, 3)
, '|'
, -1) end as third_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 3
then substring_index(substring_index(path
, '|'
, 4)
, '|'
, -1) end as fourth_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 4
then substring_index(substring_index(path
, '|'
, 5)
, '|'
, -1) end as fifth_achievement_id
from student_achievement
;
查询结果: