Mysql中substring_index函數(shù)實現(xiàn)字符分割一行變多行

更新時間：2023年12月22日 09:27:12 作者：一個處女座-代碼潔癖的程序猿

在MySQL中,字符串分割是一個常見的操作,本文主要介紹了Mysql中substring_index函數(shù)實現(xiàn)字符分割一行變多行,具有一定的參考價值,感興趣的可以了解一下

問題

有時候我們表里會存在某個字符(例如經常用到的,)分割的分割的數(shù)據(jù)，但是在使用的時候要將存在字符分割數(shù)據(jù)拆分，我們一般的做法先從數(shù)據(jù)庫中將數(shù)據(jù)查詢出，在內存中通過字符串分割函數(shù)split進行處理，不會在sql中通過函數(shù)進行拆分，這也符合阿里的規(guī)范，但是如果我們是出報表，通過其他平臺寫sql的方式進行拆分展示，不經過內存字符串分割，這時候我們只能通過sql 函數(shù)substring_index來處理，下面介紹2種方式

解決方案

創(chuàng)建表

create table tb_staff_position_info
(
    id                 varchar(255)                           not null comment '主鍵'
        primary key,
    created_at         timestamp    default CURRENT_TIMESTAMP not null comment '記錄創(chuàng)建時間',
    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;

初始化數(shù)據(jù)

insert into tb_staff_position_info(staff_name,position_name)values
('小平','技術組長,產品經理,項目經理'),('小花','程序員,測試員,'),('小廣‘,'研發(fā)總監(jiān)')

預期查詢

查詢員工所屬的職位，多個職位顯示多行

整體思路

1、將position_name 以","號進行拆分，使用mysql 的substring_index(clunm,拆分字符,第幾個n) 函數(shù)，substring_index函數(shù)每次只能獲取一個職位

2、借助臨時表，從0開始自增，數(shù)據(jù)中最多有多少個分割字符，臨時表就有多少條數(shù)據(jù)

方案一

借助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來實現(xiàn)

方案二

有時候查詢用戶的權限無法訪問mysql的表，那么我們也可以創(chuàng)建一個臨時表替換，如下所示

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 )

上述創(chuàng)建的臨時表b 最多能匹配 3個分割符，如果存在大于3個那么在將數(shù)據(jù)插入到臨時表即可