目錄

• MySQL列以逗號分隔轉(zhuǎn)成多行
• 場景
• 解決方案
• 總結(jié)

MySQL列以逗號分隔轉(zhuǎn)成多行

業(yè)務(wù)場景：

在數(shù)據(jù)庫中，有一張的一個(gè)字段存儲方式是采用以逗號分隔存儲多個(gè)值，現(xiàn)在需要將其進(jìn)行拆分成多個(gè)獨(dú)立的值，與另外一張字典表進(jìn)行關(guān)聯(lián)，取的最終的字典表中的 label，再以逗號拼接成顯示 label 的形式展現(xiàn)。

場景

表中存儲的值：

期待最終的展現(xiàn)效果：

甜品，休閑食品，飲料

解決方案

1. 將一列轉(zhuǎn)成多行

select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ",", b.help_topic_id + 1), ",", - 1) AS exhibit      from test v1       JOIN mysql.help_topic AS b ON b.help_topic_id < (length(v1.intention_exhibits) - length(REPLACE(v1.intention_exhibits, ",", "")) + 1)where v1.id = "63591ee4f8204212837e447b34c61fef";

說明：

mysql.help_topic 表的自增id是從0開始，所以在進(jìn)行截取時(shí)要對id進(jìn)行+1。【系統(tǒng)表，不建議使用，真正的線上環(huán)境，dba 是不允許使用系統(tǒng)表的，所以，我們需要自己創(chuàng)建一張類似的表】

創(chuàng)建一張自增表，來代替 mysql.help_topic 系統(tǒng)表，自增表的值，需要大于自己業(yè)務(wù)表中逗號拆出來的集合數(shù)：

create table add_self(    id int(20) null); INSERT INTO add_self (id) VALUES (0);INSERT INTO add_self (id) VALUES (1);INSERT INTO add_self (id) VALUES (2);INSERT INTO add_self (id) VALUES (3);INSERT INTO add_self (id) VALUES (4);INSERT INTO add_self (id) VALUES (5);INSERT INTO add_self (id) VALUES (6);INSERT INTO add_self (id) VALUES (7);INSERT INTO add_self (id) VALUES (8);INSERT INTO add_self (id) VALUES (9);INSERT INTO add_self (id) VALUES (10);

2. 最終 SQL

select group_concat(edn.name)from (select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ",", b.id + 1), ",", - 1) AS exhibit      from test1 v1       JOIN add_self AS b ON b.id < (length(v1.intention_exhibits) - length(REPLACE(v1.intention_exhibits, ",", "")) + 1)    where v1.id = "63591ee4f8204212837e447b34c61fef") t    left join test2 edn on t.exhibit = edn.local_key;

使用到的相關(guān)函數(shù)：

• group_concat
• substring_index
• length

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持。