Mysql 以字符分割一行变多行(substring_index函数）

问题

有时候我们表里会存在某个字符(例如经常用到的,)分割的分割的数据，但是在使用的时候要将存在字符分割数据拆分，我们一般的做法先从数据库中将数据查询出，在内存中通过字符串分割函数split进行处理，不会在sql中通过函数进行拆分，这也符合阿里的规范，但是如果我们是出报表，通过其他平台写sql的方式进行拆分展示，不经过内存字符串分割，这时候我们只能通过sql 函数substring_index来处理，下面介绍2种方式

解决方案

创建表

create table tb_staff_position_info
(
    id                 varchar(255)                           not null comment '主键'
        primary key,
    created_at         timestamp    default CURRENT_TIMESTAMP not null comment '记录创建时间',
    updated_at         timestamp    default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '记录修改时间',
   staff_name    varchar(64)  default ''                not null comment '员工名称',
   position_name    varchar(64)  default ''                not null comment '多个职位以,号分割',
  
)
    comment '员工职位表' charset = utf8;

初始化数据

insert into tb_staff_position_info(staff_name,position_name)values
('小平','技术组长,产品经理,项目经理'),('小花','程序员,测试员,'),('小广‘,'研发总监')

预期查询

查询员工所属的职位，多个职位显示多行

整体思路

1、将position_name 以","号进行拆分，使用mysql 的substring_index(clunm,拆分字符,第几个n) 函数，substring_index函数每次只能获取一个职位

2、借助临时表，从0开始自增，数据中最多有多少个分割字符，临时表就有多少条数据

方案一

借助mysql 的自增表，如下

select 
  info.staff_name,
  substring_index(
        substring_index(
            info.position_name ,
            ',',
            b.n + 1
        ),
        ',' ,- 1
    ) AS position_name
  from tb_staff_position_info info
  join mysql.help_topic b  
on b.help_topic_id < ( length(info.position_name ) - length(REPLACE (info.position_name , ',', ''))  + 1 ) 

临时表借助于mysql的help_topic 表中的 help_topic_id来实现

方案二

有时候查询用户的权限无法访问mysql的表，那么我们也可以创建一个临时表替换，如下所示

select 
  info.staff_name,
  substring_index(
        substring_index(
            info.position_name ,
            ',',
            b.n + 1
        ),
        ',' ,- 1
    ) AS position_name
  from tb_staff_position_info info
  join (
          select 0 as n
          union all 
          select 1 as n
          union all 
          select 2 as n
          ) b 
         on b.n < ( length(info.position_name ) - length(REPLACE (info.position_name , ',', ''))  + 1 )

上述创建的临时表b 最多能匹配 3个分割符，如果存在大于3个那么在将数据插入到临时表即可

方案三

临时表变成正式表，在数据库中创建一张自增表

总结

上述方式各有千秋，下面总结优缺点

优缺点

方法一：

优点：临时表借助mysql 内置的help_topic 表，无需自己创建临时表

缺点：存在查询账号权限的限制，有的账号是无法访问mysql内置表

方法二：

优点：创建自增的临时表不存在权限的限制

缺点：如果分割符存在多个，就要插入多条数据，写法上没有方法一整洁优雅