Mysql中substring_index函數(shù)實(shí)現(xiàn)字符分割一行變多行
問題
有時(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í)表即可
方案三
臨時(shí)表變成正式表,在數(shù)據(jù)庫中創(chuàng)建一張自增表
總結(jié)
上述方式各有千秋,下面總結(jié)優(yōu)缺點(diǎn)
優(yōu)缺點(diǎn)
方法一:
優(yōu)點(diǎn):臨時(shí)表借助mysql 內(nèi)置的help_topic 表,無需自己創(chuàng)建臨時(shí)表
缺點(diǎn):存在查詢賬號權(quán)限的限制,有的賬號是無法訪問mysql內(nèi)置表
方法二:
優(yōu)點(diǎn):創(chuàng)建自增的臨時(shí)表不存在權(quán)限的限制
缺點(diǎn):如果分割符存在多個(gè),就要插入多條數(shù)據(jù),寫法上沒有方法一整潔優(yōu)雅
到此這篇關(guān)于Mysql中substring_index函數(shù)實(shí)現(xiàn)字符分割一行變多行的文章就介紹到這了,更多相關(guān)Mysql 字符分割內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SQL實(shí)現(xiàn)對數(shù)據(jù)庫檢索數(shù)據(jù)的直接轉(zhuǎn)換計(jì)算
這篇文章主要介紹了SQL實(shí)現(xiàn)對數(shù)據(jù)庫檢索數(shù)據(jù)的直接轉(zhuǎn)換計(jì)算,文章圍繞主題展開詳細(xì)的內(nèi)容介紹,具有一定的參考價(jià)值,需要的小伙伴可以參考一下2022-09-09MySQL遞歸查找樹形結(jié)構(gòu)(這個(gè)方法太實(shí)用了!)
對于數(shù)據(jù)庫中的樹形結(jié)構(gòu)數(shù)據(jù),如部門表,有時(shí)候,我們需要知道某部門的所有下屬部分或者某部分的所有上級部門,這時(shí)候就需要用到mysql的遞歸查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL遞歸查找樹形結(jié)構(gòu)的相關(guān)資料,需要的朋友可以參考下2022-11-11與MSSQL對比學(xué)習(xí)MYSQL的心得(八)--插入 更新 刪除
這一篇《與MSSQL對比學(xué)習(xí)MYSQL的心得(八)》將會(huì)講解MYSQL的插入、更新和刪除語句2014-08-08Mysql和SQLServer驅(qū)動(dòng)連接的實(shí)現(xiàn)步驟
本文主要介紹了Mysql和SQL?Server的驅(qū)動(dòng)連接,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06MySQL?Prepared?Statement?預(yù)處理的操作方法
預(yù)處理語句是一種在數(shù)據(jù)庫管理系統(tǒng)中使用的編程概念,用于執(zhí)行對數(shù)據(jù)庫進(jìn)行操作的?SQL?語句,這篇文章主要介紹了MySQL?Prepared?Statement?預(yù)處理?,需要的朋友可以參考下2024-08-08MySQL使用MD5加密算法進(jìn)行數(shù)據(jù)加密功能
在現(xiàn)代的數(shù)據(jù)庫應(yīng)用中,數(shù)據(jù)的安全性和隱私性變得尤為重要,MySQL作為最流行的關(guān)系型數(shù)據(jù)庫之一,提供了多種加密功能,允許用戶對數(shù)據(jù)進(jìn)行加密和解密操作,在這篇文章中,我們將深入探討MySQL的加密功能,并重點(diǎn)介紹如何使用MD5加密算法進(jìn)行加密,需要的朋友可以參考下2024-12-12