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

2023-02-08 22:03:00

MySQL列以逗號分隔轉成多行

業務場景：

在資料庫中，有一張的一個欄位儲存方式是採用以逗號分隔儲存多個值，現在需要將其進行拆分成多個獨立的值，與另外一張字典表進行關聯，取的最終的字典表中的 label，再以逗號拼接成顯示 label 的形式展現。

場景

表中儲存的值：

期待最終的展現效果：

甜品，休閒食品，飲料

解決方案

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。【系統表，不建議使用，真正的線上環境，dba 是不允許使用系統表的，所以，我們需要自己建立一張類似的表】

建立一張自增表，來代替 mysql.help_topic 系統表，自增表的值，需要大於自己業務表中逗號拆出來的集合數：

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;

使用到的相關函數：

group_concat
substring_index
length

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

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

目錄

MySQL列以逗號分隔轉成多行

場景

解決方案

總結

熱門文章