MySQL中列如何以逗號分隔轉成多行

更新時間：2023年02月07日 09:46:57 作者：搬運Gong

這篇文章主要介紹了MySQL中列如何以逗號分隔轉成多行問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

MySQL列以逗號分隔轉成多行

業(yè)務場景：

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

場景

表中存儲的值：

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

甜品，休閑食品，飲料

解決方案

1. 將一列轉成多行

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開始，所以在進行截取時要對id進行+1?！鞠到y(tǒng)表，不建議使用，真正的線上環(huán)境，dba 是不允許使用系統(tǒng)表的，所以，我們需要自己創(chuàng)建一張類似的表】

創(chuàng)建一張自增表，來代替 mysql.help_topic 系統(tǒng)表，自增表的值，需要大于自己業(yè)務表中逗號拆出來的集合數(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;