MySQL查詢字段實現字符串分割split功能的示例代碼

Posted on by

問題背景

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

場景模擬

現有表【ec_logicplace】，如下圖所示：

要求判斷數值【1】是否存在於表【ec_logicplace】中的【actual_place_id】中

方法實現

首先將【actual_place_id】字段用逗號拆分查詢出來

 通用模板為：

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

上述所用的關鍵字包含【SUBSTRING_INDEX】，【LENGTH】，【REPLACE】。

SUBSTRING_INDEX

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

SUBSTRING_INDEX(str,delim,count)

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

舉例：

獲取第二個以“，”逗號為分隔符之前的所有字符

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

獲取倒數第二個以“，”逗號為分隔符之前的所有字符

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

LENGTH

獲取字符串的長度，格式為：

LENGTH(str)

參數 含義
str 需要計算長度的字符串

舉例：

獲取’a,b,c‘字符串的長度

LENGTH('a,b,c')

REPLACE

替換函數，格式為：

replace(str,from_str,to_str)
參數 含義
str 需要進行替換的字符串
from_str 需要被替換的字符串
to_str 需要替換的字符串

舉例：

將分隔符“，”逗號替換成“、”頓號

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

SQL解析

此處用的是MySQL庫的help_topic 表的help_topic_id 來作為變量，因為help_topic_id 是自增的。

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

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

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

step2:根據逗號進行拆分字符串，也就是SUBSTRING_INDEX函數，最後把結果賦值給num字段

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

需要註意的是，這裡使用的是MySQL中的內置表help_topic，裡面有508條數據（不同版本數據條數有差別），用戶需要有對該表查詢的權限，這樣的話隻滿足分割數量少於508條的字符串，否則應該自定義輔助表，設置更大的一個遞增列

當需要分割的字符串是查詢出來的時候

當需要分割的字符串是查詢出來的時候，可能不止一條數據，直接嵌入模板SQL會報錯

 這時候可以使用存儲過程來進行處理，也就是本次遇到的問題

要求判斷數值【1】是否存在於表【ec_logicplace】中的【actual_place_id】中

 首先創建一個存儲過程，一個輸入參數一個輸出參數，輸入參數為需要判斷的值，輸出參數為判斷結果，我們以0，1來進行判斷，0是輸入參數不存在於表字段中，1是輸入參數存在於表字段中。存儲過程如下

CREATE DEFINER=`root`@`%` PROCEDURE `queryActualInLogic`(IN `actualPlaceId` bigint,OUT `isContain` int)
BEGIN
	DECLARE v_column VARCHAR(100);
	-- 設置終止標記
	declare done int default 0;
	
	# 查詢出所有待判斷的字段值集合
	declare table_loop cursor for 
		SELECT actual_place_id FROM ec_logicplace WHERE actual_place_id IS NOT NULL;
	# 捕獲系統拋出的 not found 錯誤，如果捕獲到，將 done 設置為 1  相當於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;
		
		# 遍歷字段值，拆分後進行判斷，存在則賦值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;
		#執行語句
		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

測試：

後續MyBatis調用存儲過程的返回值進行業務判斷見

MyBatis調用MySQL存儲過程，有返回參數 

到此這篇關於MySQL查詢字段實現字符串分割split功能的示例代碼的文章就介紹到這瞭,更多相關MySQL字符串分割內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：