一、需求

1 某一個(gè)字段中的數(shù)據(jù)為字符串拼接，要求按照特定分隔符分割成多行

2 例如（movies）表

拆分結(jié)果為：

一、pg數(shù)據(jù)庫(kù)中一行變多行

1 在pg數(shù)據(jù)庫(kù)中實(shí)現(xiàn)這種拆分有指定的函數(shù)：UNNEST(STRING_TO_ARRAY(字段,'分隔符'))。

2 STRING_TO_ARRAY(字段，‘分隔符’)；將字符串 -----> {字符串} 數(shù)組。

3 UNNEST 將數(shù)組拆分

4 代碼

	 SELECT 
		id,name,hobbies,
		UNNEST(string_to_array(hobbies, '、')) as tmp
	FROM movies

二、mysql數(shù)據(jù)庫(kù)中一行變多行

注：mysql 中沒(méi)有指定的函數(shù)來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)中一行變多行。

2.1  實(shí)現(xiàn)方式

（1）利用遞歸不停的循環(huán)拆分 -- （mysql版本必須在5.8+ 才可以用）

（2）利用一個(gè)臨時(shí)表。要求：臨時(shí)表中某一個(gè)字段的值必須是int類(lèi)型且是聯(lián)系遞增的。

2.2 遞歸方式

1 代碼實(shí)現(xiàn)

	WITH RECURSIVE m(id,name,hobbies,str) AS (
	
		SELECT id,name,substr(hobbies,1,instr(hobbies,'、')-1),suubstr(concat(class,'、'),instr(class,'、')+1) 
		FROM movies m
		UNION ALL
		SELECT id,name,substr(str,1,instr(str,'、')-1),suubstr(str,instr(class,'、')+1) 
		FROM m
		WHERE instr(str,'、')>0
	)
	SELECT * FROM m ORDER BY id;

2.3 方式二  

 1 實(shí)現(xiàn)思路：我們利用substring_index函數(shù)按照指定分隔符截取字符串。

2 substring_index(str,delimiter,number) 返回從字符串 str 的第 number 個(gè)出現(xiàn)的分隔符 delimiter 之前的子串。

3 如果 number 是正數(shù)，那么就是從左往右，返回第 number 個(gè)分隔符的左邊的全部?jī)?nèi)容；number為負(fù)，則相反。例如 substring_index(hobbies,'、',2)，獲得的結(jié)果為：

4 問(wèn)題：hobbies 中每個(gè)字符串的長(zhǎng)度是未知的，因此我們?nèi)绾闻袛嘁厝状危课覀儜?yīng)該正向截取還是反向截??？ 

以 id=1 為例：

（1）經(jīng)過(guò)分析可知，反向截取會(huì)更容易。

第一次：substring_index('動(dòng)畫(huà)','、',-1) --- 得到：動(dòng)畫(huà)

第二次：substring_index('動(dòng)畫(huà)、劇情','、',-1) --- 得到：劇情

第三次：substring_index('動(dòng)畫(huà)、劇情、奇幻','、',-1) --- 得到：奇幻

結(jié)束分割。

（2）若想實(shí)現(xiàn)上述步驟，需要?jiǎng)討B(tài)的自動(dòng)改變字段hobbies的值，將本次得到的數(shù)據(jù)給截取掉。問(wèn)題：① 如何動(dòng)態(tài)截取hobbies。② 截取到什么時(shí)候結(jié)束。

5 解決：

問(wèn)題一：仍然利用string_index 函數(shù)進(jìn)行截取

substring_index(hobbies, '、', 1) --- '動(dòng)畫(huà)'

substring_index(hobbies, '、', 2) --- '動(dòng)畫(huà)、劇情'

substring_index(hobbies, '、', 3) --- '動(dòng)畫(huà)、劇情、奇幻'

第一次：substring_index(substring_index(hobbies, '、', 1), '、', -1) --- 得到：動(dòng)畫(huà)

第二次：substring_index(substring_index(hobbies, '、', 2), '、' ,-1) --- 得到：劇情

第三次：substring_index(substring_index(hobbies, '、', 3), '、' ,-1) --- 得到：奇幻

這里的1、2、3 可以換成臨時(shí)表（tmp）中的自增id 即： substring_index(substring_index(hobbies, '、', id), '、' ,-1)。

問(wèn)題二：截取多少次為止？

（1）我們可以看到有幾個(gè)分隔符，就截取 分隔符數(shù)+1次。比如：'動(dòng)畫(huà)、劇情、奇幻' 有 2 個(gè)分隔符，根據(jù)上述描述可以看出需要分割3次。同理 '劇情、愛(ài)情' ，有 1 個(gè)分隔符，但是需要分割2次。

（2）因此需要截取多少次為止的問(wèn)題，就轉(zhuǎn)化為了求字符串中有幾個(gè)分隔符的問(wèn)題。

（3）解決：利用原始字符串的長(zhǎng)度 - 除去分隔符的字符串的長(zhǎng)度 = 所有分隔符的長(zhǎng)度 = 分隔符個(gè)數(shù) * 分隔符所占字節(jié)【LENGTH(hobbies)-LENGTH(REPLACE(hobbies, '、', ''))】。注意：中文分隔符占兩個(gè)字節(jié)，因此一般我們可以現(xiàn)將分隔符轉(zhuǎn)為英文字符，之后在進(jìn)行計(jì)算，這樣直接得到分隔符個(gè)數(shù)。即：LENGTH(REPLACE(hobbies, '、', ','))-LENGTH(REPLACE(hobbies, '、', '')) = n。

（4）因此每一行拆分的結(jié)束條件：temp.id <LENGTH(hobbies)-LENGTH(REPLACE(hobbies, '、', ''))

6 代碼

SELECT 
	m.id,m.name,m.hobbies,
	SUBSTRING_INDEX(SUBSTRING_INDEX(m.hobbies,'、',b.help_topic_id+1),'、',-1) as tmp
		FROM `movies`m
	 inner join mysql.help_topic b
	 on b.help_topic_id<LENGTH(REPLACE(m.hobbies,'、',',')) -LENGTH(REPLACE(m.hobbies,'、','')) +1

這里用的臨時(shí)表是mysql 自帶的help_topic表。由于該表 id 是從 0 開(kāi)始的，因此在進(jìn)行判斷時(shí)要 +1。

注意：當(dāng)我們系統(tǒng)上線(xiàn)后，可能由于權(quán)限或者其他問(wèn)題導(dǎo)致我們不能訪(fǎng)問(wèn) mysql 中自帶的表。因此為了保險(xiǎn)起見(jiàn)，可以自己創(chuàng)建一個(gè)臨時(shí)表。 （只要保證其中一個(gè)字段是int類(lèi)型，且連續(xù)自增即可）

到此這篇關(guān)于sql拆分字符串實(shí)現(xiàn)一行變多行的實(shí)例代碼的文章就介紹到這了,更多相關(guān)sql拆分字符串內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論