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

MSSQL 與 MySQL 自定義函數/存儲過程對比：字串分割實戰

編輯：MySQL 資料庫相關知識

需求

輸入 'A,B,C'，輸出單欄結果集：

A
B
C

MSSQL：Table-Valued Function（回傳表變數）

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

-- 使用
SELECT * FROM dbo.StrSplit('52,50,55', ',');

MySQL：不支援回傳 TABLE 的函數 → 改用存儲過程 + 臨時表

-- 前置：允許建立函數（主從複制環境需開啟）
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //

DROP PROCEDURE IF EXISTS split_string //
CREATE PROCEDURE split_string (
    IN input VARCHAR(2000),
    IN delimiter VARCHAR(10)
)
SQL SECURITY INVOKER
COMMENT '分割字串存入臨時表 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 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,Jay,Kaj,Colin', ',');
SELECT * FROM SplitValues;

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

關鍵差異總結

特性	MSSQL	MySQL
回傳表值函數	✅ 原生支援 `RETURNS @t TABLE`	❌ 不支援（至 8.0 仍無）
臨時表	`#temp` (會話級)、`##temp` (全域)	`CREATE TEMPORARY TABLE` (會話級，連線斷開自動刪)
分隔符處理	`CHARINDEX`/`STUFF`	`INSTR`/`SUBSTRING`/`LEFT`
記憶體表	無直接對應	`ENGINE=MEMORY` 極速、適合暫存
調用方式	`SELECT * FROM fn(...)`	`CALL sp(...); SELECT * FROM temp_table;`

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;

CTE 寫法更簡潔、無副作用、可直接嵌入查詢，推薦 8.0+ 環境使用。