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

字符串分割在 MSSQL 與 MySQL 的異構實現：函數 vs 預存程序

編輯：MySQL 資料庫相關知識

需求場景

輸入 'A,B,C'，期望輸出三行：A、B、C。雙系統均無內建 SPLIT_STRING (SQL Server 2016+ 才引入)，需自行實現。

MSSQL：內聯表值函數 (高性能、可 JOIN)

CREATE FUNCTION dbo.StrSplit
(
    @c VARCHAR(2000),
    @split VARCHAR(2) = ','
)
RETURNS @t TABLE (col VARCHAR(100))
AS
BEGIN
    DECLARE @pos INT;
    WHILE (CHARINDEX(@split, @c) > 0)
    BEGIN
        SET @pos = CHARINDEX(@split, @c);
        INSERT @t(col) VALUES (SUBSTRING(@c, 1, @pos - 1));
        SET @c = STUFF(@c, 1, @pos, '');
    END
    INSERT @t(col) VALUES (@c);  -- 最後一段
    RETURN;
END
GO

-- 使用：無縫 JOIN
SELECT * FROM dbo.StrSplit('52,50,55', ',');
SELECT t.* FROM Orders o CROSS APPLY dbo.StrSplit(o.tag_ids, ',') t;

優勢：返回 Table 類型，可直接 CROSS APPLY 參與關聯查詢，優化器可下推謂詞。

MySQL：臨時表 + 預存程序 (受限於函數不支持返回 TABLE)

DELIMITER //

DROP PROCEDURE IF EXISTS split_string //
CREATE PROCEDURE split_string (
    IN input VARCHAR(2000),
    IN delimiter VARCHAR(10)
)
SQL SECURITY INVOKER
COMMENT 'Splits string into temporary table SplitValues'
BEGIN
    DECLARE cur_pos INT DEFAULT 1;
    DECLARE remainder VARCHAR(2000);
    DECLARE cur_str VARCHAR(1000);
    DECLARE delim_len TINYINT UNSIGNED;

    DROP TEMPORARY TABLE IF EXISTS SplitValues;
    CREATE TEMPORARY TABLE SplitValues (
        value VARCHAR(1000) NOT NULL PRIMARY KEY
    ) ENGINE=Memory;  -- 內存表極快

    SET remainder = input;
    SET delim_len = CHAR_LENGTH(delimiter);

    WHILE CHAR_LENGTH(remainder) > 0 AND cur_pos > 0 DO
        SET cur_pos = INSTR(remainder, delimiter);
        IF cur_pos = 0 THEN
            SET cur_str = remainder;
        ELSE
            SET cur_str = LEFT(remainder, cur_pos - 1);
        END IF;
        IF TRIM(cur_str) != '' THEN
            INSERT IGNORE INTO SplitValues VALUES (cur_str);
        END IF;
        SET remainder = SUBSTRING(remainder, cur_pos + delim_len);
    END WHILE;
END //

DELIMITER ;

-- 使用：兩步走
CALL split_string('Steve,Jan,Bobby', ',');
SELECT * FROM SplitValues;

-- 進階：JOIN 過濾
CREATE TEMPORARY TABLE TestJoin (join_field VARCHAR(20) PRIMARY KEY) ENGINE=Memory;
INSERT INTO TestJoin VALUES ('Jan'),('Bobby');
SELECT tj.* FROM TestJoin tj INNER JOIN SplitValues sv ON tj.join_field = sv.value;

關鍵差異對比

維度	MSSQL 函數	MySQL 預存程序
返回類型	Table 變量 (內聯)	臨時表 (會話級可見)
調用方式	`SELECT * FROM fn(...)`	`CALL sp(...); SELECT * FROM tmp;`
可組合性	極高 (`CROSS/OUTER APPLY`)	中等 (需顯式 JOIN 臨時表)
併發隔離	自動 (函數內部變量)	臨時表名衝突風險，建議加 Session ID 前綴
性能	優化器深度整合	Memory 引擎極快，但需兩次網絡往返

MySQL 8.0+ 現代寫法：遞歸 CTE (無需預存程序)

WITH RECURSIVE split_cte(val, rest) AS (
    SELECT 
        SUBSTRING_INDEX('A,B,C', ',', 1),
        SUBSTRING('A,B,C', LENGTH(SUBSTRING_INDEX('A,B,C', ',', 1)) + 2)
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(rest, ',', 1),
        SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2)
    FROM split_cte
    WHERE rest != ''
)
SELECT val FROM split_cte;

單條 SQL 即可完成，無副作用、可直接嵌入大查詢，推薦為首選方案。