mysql字符串分割多列展示

这里写目录标题

• 前言
• 函数介绍
• • substring_index
  • length
• sql 举例

前言

由于某些特殊需求，需要直接从数据库中取数据交给甲方自己处理，因此对一些特殊字符串进行处理，比如 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:计数

• 1
• 2
• 3
• 4

即 字符串截取函数，例子：str = 1|2|3|4|5

substring_index(str,'|',1)=1
substring_index(str,'|',2)=1|2

• 1
• 2

也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容；相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如 substring_index(str,‘|’,-1)=5
如果要获得中间的数据，只需要调用两边函数即可，即先获取左边全部内容，然后获取右边的第一个，如

substring_index(substring_index(str,'|',2),'|',-1)=2

• 1

length

length(str)
str:要处理的字符串

• 1
• 2

即 查看字符串的长度，注意一个汉字是算三个字符,一个数字或字母算一个字符
由于本需求需要列出所有层级数据，但层级并不是固定的，因此需要额外判断当前数据有几层，即判断有几个 | 符号，可以使用当前函数实现，即：

(LENGTH('1|2|3|4|5') - LENGTH(REPLACE ('1|2|3|4|5', '|', '')))=4

• 1

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
             ;

查询结果：