Mysql 以字符分割一行变多行(substring
作者:mmseoamin日期:2023-12-20

问题

有时候我们表里会存在某个字符(例如经常用到的,)分割的分割的数据,但是在使用的时候要将存在字符分割数据拆分,我们一般的做法先从数据库中将数据查询出,在内存中通过字符串分割函数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内置表

方法二:

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

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