mysql根據逗號將一行數據拆分成多行數據

Posted on by

分隔效果

— 分隔前
1,2,3,4
— 分隔後
1
2
3
4

分隔命令行 

SELECT 
 substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) result
FROM 
 mysql.help_topic b
where 
 b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1);

命令行解釋

help_topic本身是Mysql一個幫助解釋註釋表，用於解釋Mysql各種專有名詞，由於這張表數據ID是從0順序增加的，方便我們用於計數，但是8.0.17版本的隻有686條數據，超過這個數字，我們就需要己自定義一張表

可以用做計數的臨時表，查詢的語句隻會用help_topic計數，超出的部分其實都是臟數據

b.help_topic_id < 獲取分隔後的總行數

b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1)

這個語句其實是一個遍歷，help_topic_id 從0開始遞增到

按照順序截取，先截取第 n 個分隔符之前的所有字符，再截取字符串最後一位

1    —— 1
1,2  —— 2
1,2,3 —— 3
1,2,3,4 —— 4

mysql.help_topic 無權限處理辦法

mysql.help_topic 的作用是對 SUBSTRING_INDEX 函數出來的數據(也就是按照分割符分割出來的)數據連接起來做笛卡爾積。

如果 mysql.help_topic 沒有權限，可以自己創建一張臨時表，用來與要查詢的表連接查詢。

獲取該字段最多可以分割成為幾個字符串:

SELECT MAX(LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ',', '' )) + 1) FROM `test` a;

創建臨時表，並給臨時表添加數據:

註意:

  • 臨時表必須有一列從 0 或者 1 開始的自增數據
  • 臨時表表名隨意，字段可以隻有一個
  • 臨時表示的數據量必須比 MAX(LENGTH(a.name) – LENGTH(REPLACE(a.name, ‘,’, ” )) + 1) 的值大

涉及函數

substring_index(str，delim，count)

參數名 解釋
str 需要拆分的字符串
delim 分隔符，通過某字符進行拆分
count 當 count 為正數，取第 n 個分隔符之前的所有字符； 當 count 為負數，取倒數第 n 個分隔符之後的所有字符。

replace( str, from_str, to_str)

參數名 解釋
str 需要進行替換的字符串
from_str 需要被替換的字符串
to_str 需要替換的字符串

length(str) 獲取字符串長度

到此這篇關於mysql根據逗號將一行數據拆分成多行數據的文章就介紹到這瞭,更多相關mysql根據逗號拆分內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：