MySQL 存儲過程實現字符串分割 (SPLIT) 功能完整示例

需求場景

MySQL 內建無 SPLIT_STRING 函數，需自行實現將逗號分隔字符串拆分為臨時表記錄，供後續查詢使用。

完整存儲過程代碼

DROP PROCEDURE IF EXISTS `procedure_split`;

DELIMITER $$

CREATE PROCEDURE `procedure_split`(
    IN `inputstring` VARCHAR(1000),
    IN `delim` CHAR(1)
)
BEGIN
    DECLARE `strlen` INT DEFAULT LENGTH(`inputstring`);
    DECLARE `last_index` INT DEFAULT 0;
    DECLARE `cur_index` INT DEFAULT 1;
    DECLARE `cur_char` VARCHAR(200);
    DECLARE `len` INT;

    -- 建立臨時表存儲分割結果
    DROP TEMPORARY TABLE IF EXISTS `splittable`;
    CREATE TEMPORARY TABLE `splittable` (
        `value` VARCHAR(20)
    );

    WHILE `cur_index` <= `strlen` DO
        BEGIN
            -- 取當前字符
            SET `cur_char` = SUBSTRING(`inputstring`, `cur_index`, 1);
            
            -- 遇到分隔符或到達字符串末尾
            IF `cur_char` = `delim` OR `cur_index` = `strlen` THEN
                SET `len` = `cur_index` - `last_index` - 1;
                
                -- 處理最後一個字符非分隔符的情況
                IF `cur_index` = `strlen` AND `cur_char` != `delim` THEN
                    SET `len` = `len` + 1;
                END IF;
                
                -- 插入分割出的子串
                INSERT INTO `splittable` (`value`)
                VALUES (SUBSTRING(`inputstring`, `last_index` + 1, `len`));
                
                SET `last_index` = `cur_index`;
            END IF;
            
            SET `cur_index` = `cur_index` + 1;
        END;
    END WHILE;
END$$

DELIMITER ;

調用示例

-- 調用存儲過程分割字符串
CALL `procedure_split`('分享,代碼,片段', ',');

-- 查詢分割結果
SELECT * FROM `splittable`;

輸出結果：

+-------+
| value |
+-------+
| 分享  |
| 代碼  |
| 片段  |
+-------+

核心邏輯解析

1. 字符遍歷： 使用 WHILE 循環逐字符掃描輸入字符串
2. 邊界判斷： 遇到分隔符 delim 或到達字符串末尾 cur_index = strlen 時觸發截取
3. 長度計算： len = cur_index - last_index - 1 計算子串長度，末尾特殊處理 +1
4. 子串提取： SUBSTRING(inputstring, last_index+1, len) 從上一分隔符後開始截取
5. 臨時表存儲： 結果存入 splittable，會話結束自動銷毀

改進版：支援多字符分隔符與去重

CREATE PROCEDURE `procedure_split_v2`(
    IN `input_str` TEXT,
    IN `delimiter` VARCHAR(10),
    IN `distinct_flag` TINYINT(1)  -- 1=去重
)
BEGIN
    DECLARE `pos` INT DEFAULT 1;
    DECLARE `delim_len` INT DEFAULT LENGTH(`delimiter`);
    DECLARE `token` VARCHAR(255);
    
    DROP TEMPORARY TABLE IF EXISTS `splittable`;
    CREATE TEMPORARY TABLE `splittable` (`value` VARCHAR(255));
    
    IF `delim_len` = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delimiter cannot be empty';
    END IF;
    
    REPEAT
        SET `pos` = LOCATE(`delimiter`, `input_str`, `pos`);
        IF `pos` = 0 THEN
            SET `token` = TRIM(`input_str`);
            SET `input_str` = '';
        ELSE
            SET `token` = TRIM(SUBSTRING(`input_str`, 1, `pos` - 1));
            SET `input_str` = SUBSTRING(`input_str`, `pos` + `delim_len`);
        END IF;
        
        IF `token` != '' THEN
            IF `distinct_flag` = 1 THEN
                INSERT IGNORE INTO `splittable` (`value`) VALUES (`token`);
            ELSE
                INSERT INTO `splittable` (`value`) VALUES (`token`);
            END IF;
        END IF;
    UNTIL `input_str` = '' END REPEAT;
END

應用場景

• 前端傳入 ID 列表 (如 '1,3,5,8') 需轉為 IN 子查詢條件
• 標籤系統：文章標籤存儲為逗號分隔字符串，查詢時需拆分統計
• 批量操作參數解析

效能提示

存儲過程分割適合**中小數據量** (百~千級)。大規模分割建議：

1. 應用層 (Java/PHP/Python) 分割後拼接 SQL
2. 使用 JSON_TABLE (MySQL 8.0+) 或 REGEXP 輔助
3. 永久表替代臨時表，配合索引優化連接查詢