MySQL查詢字段實(shí)現(xiàn)字符串分割split功能的示例代碼

更新時(shí)間：2022年01月17日 11:18:03 作者：唯空城

本文主要介紹了MySQL查詢字段實(shí)現(xiàn)字符串分割split功能的示例代碼，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

問題背景

查詢MySQL中用逗號(hào)分隔的字段【a,b,c】是否包含【a】

場(chǎng)景模擬

現(xiàn)有表【ec_logicplace】，如下圖所示：

要求判斷數(shù)值【1】是否存在于表【ec_logicplace】中的【actual_place_id】中

方法實(shí)現(xiàn)

首先將【actual_place_id】字段用逗號(hào)拆分查詢出來

通用模板為：

SELECT
	SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c', ',', help_topic_id + 1 ), ',',- 1 ) AS num 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < LENGTH( 'a,b,c' ) - LENGTH( REPLACE ( 'a,b,c', ',', '' ) ) + 1

上述所用的關(guān)鍵字包含【SUBSTRING_INDEX】，【LENGTH】，【REPLACE】。

SUBSTRING_INDEX

用于字符串拆分，格式為：

SUBSTRING_INDEX(str,delim,count)

參數(shù)	含義
str	需要拆分的字符串
delim	分隔符，通過某字符進(jìn)行拆分
count	當(dāng) count 為正數(shù)，取第 n 個(gè)分隔符之前的所有字符；當(dāng) count 為負(fù)數(shù)，取倒數(shù)第 n 個(gè)分隔符之后的所有字符。

舉例：

獲取第二個(gè)以“，”逗號(hào)為分隔符之前的所有字符

SUBSTRING_INDEX('a,b,c',',',2)

獲取倒數(shù)第二個(gè)以“，”逗號(hào)為分隔符之前的所有字符

SUBSTRING_INDEX('a,b,c',',',-2)

LENGTH

獲取字符串的長(zhǎng)度，格式為：

LENGTH(str)

參數(shù)	含義
str	需要計(jì)算長(zhǎng)度的字符串

舉例：

獲取’a,b,c‘字符串的長(zhǎng)度

LENGTH('a,b,c')

REPLACE

替換函數(shù)，格式為：

replace(str,from_str,to_str)

參數(shù)	含義
str	需要進(jìn)行替換的字符串
from_str	需要被替換的字符串
to_str	需要替換的字符串

舉例：

將分隔符“，”逗號(hào)替換成“、”頓號(hào)

REPLACE('a,b,c',',','、')

SQL解析

此處用的是MySQL庫(kù)的help_topic 表的help_topic_id 來作為變量，因?yàn)閔elp_topic_id 是自增的。

原理：把要拆分的字符串拆分，首先需要知道最后要被拆分成多少個(gè)字符串，也就是上述所說的count,其次是需要知道用什么來進(jìn)行拆分。所以分為兩個(gè)步驟來進(jìn)行sql編寫

step1:獲取最后需被拆分成多少個(gè)字符串，用help_topic_id 來模擬遍歷第n個(gè)字符串：

help_topic_id < LENGTH('a,b,c')-LENGTH(REPLACE('a,b,c',',',''))+1

step2:根據(jù)逗號(hào)進(jìn)行拆分字符串，也就是SUBSTRING_INDEX函數(shù)，最后把結(jié)果賦值給num字段

SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c',',',help_topic_id+1),',',-1) AS num

需要注意的是，這里使用的是MySQL中的內(nèi)置表help_topic，里面有508條數(shù)據(jù)（不同版本數(shù)據(jù)條數(shù)有差別），用戶需要有對(duì)該表查詢的權(quán)限，這樣的話只滿足分割數(shù)量少于508條的字符串，否則應(yīng)該自定義輔助表，設(shè)置更大的一個(gè)遞增列

當(dāng)需要分割的字符串是查詢出來的時(shí)候

當(dāng)需要分割的字符串是查詢出來的時(shí)候，可能不止一條數(shù)據(jù)，直接嵌入模板SQL會(huì)報(bào)錯(cuò)

這時(shí)候可以使用存儲(chǔ)過程來進(jìn)行處理，也就是本次遇到的問題

要求判斷數(shù)值【1】是否存在于表【ec_logicplace】中的【actual_place_id】中

首先創(chuàng)建一個(gè)存儲(chǔ)過程，一個(gè)輸入?yún)?shù)一個(gè)輸出參數(shù)，輸入?yún)?shù)為需要判斷的值，輸出參數(shù)為判斷結(jié)果，我們以0，1來進(jìn)行判斷，0是輸入?yún)?shù)不存在于表字段中，1是輸入?yún)?shù)存在于表字段中。存儲(chǔ)過程如下

CREATE DEFINER=`root`@`%` PROCEDURE `queryActualInLogic`(IN `actualPlaceId` bigint,OUT `isContain` int)
BEGIN
	DECLARE v_column VARCHAR(100);
	-- 設(shè)置終止標(biāo)記
	declare done int default 0;
	
	# 查詢出所有待判斷的字段值集合
	declare table_loop cursor for 
		SELECT actual_place_id FROM ec_logicplace WHERE actual_place_id IS NOT NULL;
	# 捕獲系統(tǒng)拋出的 not found 錯(cuò)誤，如果捕獲到，將 done 設(shè)置為 1  相當(dāng)于try異常
	declare continue handler for not found set done=1;
	
	open table_loop;
	out_loop:LOOP
		# 遍歷字段值，一一賦值
		FETCH NEXT FROM table_loop into v_column;
		IF done = 1 THEN
		    LEAVE out_loop;
		END IF;
		
		# 遍歷字段值，拆分后進(jìn)行判斷，存在則賦值1，不存在則賦值0
		SET @STMT = CONCAT("SELECT COALESCE(count(1),0) AS isContain into @v_count FROM 
			(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( '",v_column,"' , ',', help_topic_id + 1 ), ',', -1 ) AS num 
				FROM mysql.help_topic WHERE
				help_topic_id < LENGTH( '",v_column,"' ) - LENGTH ( REPLACE ( '",v_column,"' , ',', '' ) ) + 1 
			) t WHERE t.num = ",actualPlaceId,";");
		PREPARE STMT FROM @STMT;
		#執(zhí)行語(yǔ)句
		EXECUTE STMT;
		deallocate prepare STMT;
		
		set isContain = @v_count;
		if isContain = 1 THEN
			LEAVE out_loop;
		END IF;
		
	END LOOP out_loop;
	close table_loop;
END

測(cè)試：