快捷導(dǎo)航

MySQL中列如何以逗號(hào)分隔轉(zhuǎn)成多行

更新時(shí)間：2023年02月07日 09:46:57 作者：搬運(yùn)Gong

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

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

業(yè)務(wù)場(chǎng)景：

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

場(chǎng)景

表中存儲(chǔ)的值：

期待最終的展現(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í)要對(duì)id進(jìn)行+1。【系統(tǒng)表，不建議使用，真正的線上環(huán)境，dba 是不允許使用系統(tǒng)表的，所以，我們需要自己創(chuàng)建一張類似的表】

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