MySQL 實現 Split 分割字符串函數：三種方案對比

方案一：臨時表返回集合（SQL Server 風格，MySQL 不直接支持）

原文給出的是 T-SQL 語法（RETURNS @t TABLE），MySQL 不支持返回表的函數。MySQL 8.0+ 可用 JSON_TABLE 或 CTE 替代。

方案二：計算分割後元素個數

CREATE FUNCTION `Get_StrArrayLength`(str VARCHAR(1024), split VARCHAR(10))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE len INT DEFAULT 1;
    DECLARE pos INT;
    SET str = TRIM(str);
    SET pos = LOCATE(split, str);
    WHILE pos > 0 DO
        SET len = len + 1;
        SET pos = LOCATE(split, str, pos + CHAR_LENGTH(split));
    END WHILE;
    RETURN len;
END;

用法：SELECT Get_StrArrayLength('78,1,2,3', ','); -- 返回 4

方案三：按索引取第 N 個元素

CREATE FUNCTION `Get_StrArrayStrOfIndex`(str VARCHAR(1024), split VARCHAR(10), idx INT)
RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE start_pos INT DEFAULT 1;
    DECLARE delim_len INT DEFAULT CHAR_LENGTH(split);
    DECLARE cur_idx INT DEFAULT 1;
    DECLARE pos INT;
    
    SET str = TRIM(str);
    SET pos = LOCATE(split, str);
    
    WHILE pos > 0 AND cur_idx < idx DO
        SET start_pos = pos + delim_len;
        SET pos = LOCATE(split, str, start_pos);
        SET cur_idx = cur_idx + 1;
    END WHILE;
    
    IF pos = 0 THEN SET pos = CHAR_LENGTH(str) + 1; END IF;
    RETURN SUBSTRING(str, start_pos, pos - start_pos);
END;

用法：SELECT Get_StrArrayStrOfIndex('8,9,4', ',', 2); -- 返回 '9'

方案四：MySQL 8.0+ 現代寫法（推薦）

遞歸 CTE 分割

WITH RECURSIVE split_cte AS (
    SELECT 
        1 AS idx,
        TRIM(SUBSTRING_INDEX('a,b,c,d', ',', 1)) AS val,
        SUBSTRING('a,b,c,d', CHAR_LENGTH(SUBSTRING_INDEX('a,b,c,d', ',', 1)) + 2) AS rest
    UNION ALL
    SELECT 
        idx + 1,
        TRIM(SUBSTRING_INDEX(rest, ',', 1)),
        SUBSTRING(rest, CHAR_LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2)
    FROM split_cte
    WHERE rest != ''
)
SELECT idx, val FROM split_cte;

JSON_TABLE 方案（8.0.4+）

SELECT jt.idx, jt.val
FROM JSON_TABLE(
    CONCAT('["', REPLACE('a,b,c,d', ',', '","'), '"]'),
    "$[*]" COLUMNS (
        idx FOR ORDINALITY,
        val VARCHAR(100) PATH "$"
    )
) AS jt;

性能對比與選型

最佳實踐建議

1. 高頻業務 應用層分割，避免 SQL 層 CPU 消耗
2. 報表/ETL 可用 CTE/JSON_TABLE，可讀性強
3. 避免在 WHERE/JOIN 中調用分割函數 → 全表掃描
4. 分隔符固定時，考慮 SUBSTRING_INDEX 組合技巧