快捷導(dǎo)航

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

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

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

問題

有時(shí)候我們表里會(huì)存在某個(gè)字符(例如經(jīng)常用到的,)分割的分割的數(shù)據(jù)，但是在使用的時(shí)候要將存在字符分割數(shù)據(jù)拆分，我們一般的做法先從數(shù)據(jù)庫中將數(shù)據(jù)查詢出，在內(nèi)存中通過字符串分割函數(shù)split進(jìn)行處理，不會(huì)在sql中通過函數(shù)進(jìn)行拆分，這也符合阿里的規(guī)范，但是如果我們是出報(bào)表，通過其他平臺(tái)寫sql的方式進(jìn)行拆分展示，不經(jīng)過內(nèi)存字符串分割，這時(shí)候我們只能通過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)建時(shí)間',
    updated_at         timestamp    default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '記錄修改時(shí)間',
   staff_name    varchar(64)  default ''                not null comment '員工名稱',
   position_name    varchar(64)  default ''                not null comment '多個(gè)職位以,號分割',
  
)
    comment '員工職位表' charset = utf8;

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

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

預(yù)期查詢

查詢員工所屬的職位，多個(gè)職位顯示多行

整體思路

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

2、借助臨時(shí)表，從0開始自增，數(shù)據(jù)中最多有多少個(gè)分割字符，臨時(shí)表就有多少條數(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 )

臨時(shí)表借助于mysql的help_topic 表中的 help_topic_id來實(shí)現(xiàn)

方案二

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

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)建的臨時(shí)表b 最多能匹配 3個(gè)分割符，如果存在大于3個(gè)那么在將數(shù)據(jù)插入到臨時(shí)表即可