您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 存儲函數實現 Split 分割：計數、取值、存表完整版

編輯：MySQL 資料庫相關知識

前置設置


SET GLOBAL log_bin_trust_function_creators = 1;  -- 允許創建函數

1. 計算分割總數


CREATE FUNCTION `func_get_splitStringTotal`(f_string VARCHAR(10000), f_delimiter VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN 1 + (CHAR_LENGTH(f_string) - CHAR_LENGTH(REPLACE(f_string, f_delimiter, '')));
END;

原理：總長 - 替換後長 = 分隔符總長度，除以分隔符長度得個數，+1 得元素數。

2. 取指定位置元素


CREATE FUNCTION `func_splitString`(f_string VARCHAR(1000), f_delimiter VARCHAR(5), f_order INT)
RETURNS VARCHAR(255) CHARSET utf8
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(255) DEFAULT '';
    -- 反轉兩次取第 N 段：SUBSTRING_INDEX 從左/右取前 N 段
    SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string, f_delimiter, f_order)), f_delimiter, 1));
    RETURN result;
END;

測試：SELECT func_splitString('1,2,3,4,5,6,7', ',', 1); -- '1'

3. 存儲過程：分割入臨時表


CREATE PROCEDURE `splitString`(IN f_string VARCHAR(1000), IN f_delimiter VARCHAR(5))
BEGIN
    DECLARE cnt INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    
    SET cnt = func_get_splitStringTotal(f_string, f_delimiter);
    
    DROP TEMPORARY TABLE IF EXISTS `tmp_split`;
    CREATE TEMPORARY TABLE `tmp_split` (`val_` VARCHAR(128) NOT NULL) DEFAULT CHARSET=utf8;
    
    WHILE i < cnt DO
        SET i = i + 1;
        INSERT INTO `tmp_split`(`val_`) VALUES (func_splitString(f_string, f_delimiter, i));
    END WHILE;
END;

調用示例


CALL splitString('a,s,d,f,g,h,j', ',');
SELECT * FROM tmp_split;
-- 輸出：a, s, d, f, g, h, j 七行

⚠️ 關鍵限制與優化

限制	影響	繞過方案
函數不支持動態 SQL	無法直接 `PREPARE`	用存儲過程
臨時表會話級可見	連接斷開自動消失	適合同會話後續處理
循環插入性能差	大數據量極慢	改用 `INSERT ... SELECT` 批量
分隔符長度固定	多字符分隔符需調整邏輯	預處理統一為單字符

現代替代方案（MySQL 8.0+）


-- 一條語句完成分割並入表
INSERT INTO target_table(col)
WITH RECURSIVE cte AS (
    SELECT 1 AS n, TRIM(SUBSTRING_INDEX('a,b,c', ',', 1)) AS val,
           SUBSTRING('a,b,c', CHAR_LENGTH(SUBSTRING_INDEX('a,b,c', ',', 1)) + 2) AS rest
    UNION ALL
    SELECT n+1, TRIM(SUBSTRING_INDEX(rest, ',', 1)),
           SUBSTRING(rest, CHAR_LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2)
    FROM cte WHERE rest != ''
)
SELECT val FROM cte;