MySQL 存儲過程實現 SPLIT 字符串分割：臨時表 + 循環解析通用方案

需求

MySQL 5.7/8.0 無內置 SPLIT_STRING 函數。需將逗號分隔字符串 'a,b,c' 拆分為多行存入臨時表供後續 JOIN/IN 查詢。

存儲過程實現

DROP PROCEDURE IF EXISTS proc_split_string;
DELIMITER $$
CREATE PROCEDURE proc_split_string (
    IN p_input_str TEXT,
    IN p_delimiter VARCHAR(10)
)
BEGIN
    DECLARE str_len INT DEFAULT LENGTH(p_input_str);
    DECLARE last_pos INT DEFAULT 1;
    DECLARE cur_pos INT DEFAULT 1;
    DECLARE token VARCHAR(255);
    DECLARE delim_len INT DEFAULT LENGTH(p_delimiter);

    -- 創建臨時表 (會話級，自動清理)
    DROP TEMPORARY TABLE IF EXISTS tmp_split_result;
    CREATE TEMPORARY TABLE tmp_split_result (
        `token` VARCHAR(255) NOT NULL,
        `pos` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=Memory; -- 內存表，極快

    -- 邊界處理：空字符串直接返回
    IF str_len = 0 OR p_input_str IS NULL THEN
        SELECT * FROM tmp_split_result;
        LEAVE proc_split_string; -- MySQL 不支持 LEAVE PROCEDURE，用標籤或 IF 包裹
    END IF;

    -- 主循環：查找分隔符位置
    split_loop: LOOP
        SET cur_pos = LOCATE(p_delimiter, p_input_str, last_pos);
        
        IF cur_pos = 0 THEN
            -- 最後一段 (無分隔符)
            SET token = TRIM(SUBSTRING(p_input_str, last_pos));
            IF token <> '' THEN
                INSERT INTO tmp_split_result (token) VALUES (token);
            END IF;
            LEAVE split_loop;
        ELSE
            -- 找到分隔符，截取
            SET token = TRIM(SUBSTRING(p_input_str, last_pos, cur_pos - last_pos));
            IF token <> '' THEN
                INSERT INTO tmp_split_result (token) VALUES (token);
            END IF;
            SET last_pos = cur_pos + delim_len;
        END IF;
    END LOOP split_loop;

    -- 返回結果
    SELECT * FROM tmp_split_result;
END$$
DELIMITER ;

調用示例

CALL proc_split_string('apple, banana, orange, grape', ',');
-- 結果：
-- +--------+-----+
-- | token  | pos |
-- +--------+-----+
-- | apple  | 1   |
-- | banana | 2   |
-- | orange | 3   |
-- | grape  | 4   |
-- +--------+-----+

-- 後續業務使用
SELECT * FROM products 
WHERE name IN (SELECT token FROM tmp_split_result);

關鍵設計點

MySQL 8.0+ 現代替代方案 (無需存儲過程)

方案一：JSON_TABLE (8.0.4+)

SELECT jt.val AS token
FROM JSON_TABLE(CONCAT('["', REPLACE('a,b,c', ',', '","'), '"]'), "$[*]" COLUMNS (val VARCHAR(255) PATH "$")) AS jt;

方案二：遞歸 CTE (8.0+)

WITH RECURSIVE split_cte AS (
    SELECT 
        TRIM(SUBSTRING_INDEX('a,b,c', ',', 1)) AS token,
        SUBSTRING('a,b,c', LENGTH(SUBSTRING_INDEX('a,b,c', ',', 1)) + 2) AS rest
    UNION ALL
    SELECT 
        TRIM(SUBSTRING_INDEX(rest, ',', 1)),
        SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2)
    FROM split_cte
    WHERE rest <> ''
)
SELECT token FROM split_cte;

方案三：應用層分割 (最推薦)

// Java
String[] tokens = input.split(",");
// 批量綁定 IN (?, ?, ?) 或臨時表批量插入

原則：字符串分割是業務邏輯，屬於應用層職責。 數據庫擅長集合運算，不擅長字符串處理。應用層分割後傳參數化數組/臨時表，性能最高、SQL 最簡潔、無版本依賴。

總結

1. MySQL 5.7 存儲過程 + 內存臨時表 是通用可行方案。
2. MySQL 8.0 優先用 JSON_TABLE / CTE 純 SQL 解決。
3. 最佳實踐：應用層分割，傳參數數組或批量臨時表。