萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 拆分字符串並判斷指定段位值:自定義函數實現

場景

字段 ColumnPath 存儲路徑字符串 '1|3|4|6',需查詢「第 3 段等於 '3'」的記錄。

T-SQL (SQL Server) 實現

-- 1. 創建分割函數(返回表值函數,性能更好)
CREATE FUNCTION dbo.SplitString
(
    @List NVARCHAR(MAX),
    @Delimiter NVARCHAR(10)
)
RETURNS @Result TABLE (Id INT IDENTITY(1,1), Value NVARCHAR(4000))
AS
BEGIN
    DECLARE @Pos INT, @NextPos INT, @Value NVARCHAR(4000);
    SET @List = @List + @Delimiter;  -- 統一結尾處理
    SET @Pos = 1;
    
    WHILE @Pos <= LEN(@List)
    BEGIN
        SET @NextPos = CHARINDEX(@Delimiter, @List, @Pos);
        IF @NextPos = 0 SET @NextPos = LEN(@List) + 1;
        SET @Value = LTRIM(RTRIM(SUBSTRING(@List, @Pos, @NextPos - @Pos)));
        INSERT INTO @Result (Value) VALUES (@Value);
        SET @Pos = @NextPos + LEN(@Delimiter);
    END
    RETURN;
END;
GO

-- 2. 使用 CROSS APPLY 查詢第 3 段 = '3'
SELECT t.*
FROM Survey_QuestionColumns t
CROSS APPLY dbo.SplitString(t.ColumnPath, '|') s
WHERE s.Id = 3 AND s.Value = '3';

MySQL 8.0+ 實現(遞歸 CTE)

WITH RECURSIVE split_cte AS (
    SELECT 
        `id`, 
        `ColumnPath`,
        1 AS seg_idx,
        SUBSTRING_INDEX(`ColumnPath`, '|', 1) AS seg_val,
        CASE WHEN LOCATE('|', `ColumnPath`) > 0 
             THEN SUBSTRING(`ColumnPath`, LOCATE('|', `ColumnPath`) + 1)
             ELSE '' END AS remainder
    FROM `Survey_QuestionColumns`
    UNION ALL
    SELECT 
        `id`, 
        `ColumnPath`,
        seg_idx + 1,
        SUBSTRING_INDEX(remainder, '|', 1),
        CASE WHEN LOCATE('|', remainder) > 0 
             THEN SUBSTRING(remainder, LOCATE('|', remainder) + 1)
             ELSE '' END
    FROM split_cte
    WHERE remainder != '' AND seg_idx < 10  -- 防無限遞歸
)
SELECT `id`, `ColumnPath` 
FROM split_cte 
WHERE seg_idx = 3 AND seg_val = '3';

MySQL 5.7 兼容方案:數字表/輔助表

-- 1. 創建數字表(一次性)
CREATE TABLE `numbers` (`n` INT PRIMARY KEY);
INSERT INTO `numbers` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

-- 2. 查詢第 3 段
SELECT t.*
FROM `Survey_QuestionColumns` t
JOIN `numbers` n ON n.n = 3
WHERE 
    -- 提取第 n 段邏輯
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.`ColumnPath`, '|', n.n), '|', -1)) = '3';

應用層處理(推薦)

數據庫只存儲,拆分邏輯下沉應用層:

// PHP 示例
function getSegment(string $path, string $delimiter, int $index): ?string {
    $parts = explode($delimiter, $path);
    return $parts[$index - 1] ?? null;  // 1-based
}

// 查詢時:先 SELECT ColumnPath,應用層過濾
$stmt = $pdo->query("SELECT id, ColumnPath FROM Survey_QuestionColumns");
$results = [];
while ($row = $stmt->fetch()) {
    if (getSegment($row['ColumnPath'], '|', 3) === '3') {
        $results[] = $row;
    }
}

方案對比

方案優點缺點適用場景
SQL Server 表值函數集成度高、可複用僅限 SQL Server、標量函數慢SQL Server 存儲過程場景
MySQL 8.0 CTE標準 SQL、無需輔助表遞歸開銷大、深度受限臨時分析、中小表
數字表 JOIN5.7 兼容、可走索引需維護數字表、語句冗長生產環境固定段位查詢
應用層拆分性能最好、邏輯靈活、可緩存需拉取全量數據、網絡開銷高頻查詢、複雜業務邏輯

反範式化建議

若頻繁按路徑段位查詢,建議冗余存儲:

ALTER TABLE `Survey_QuestionColumns` 
ADD `path_level_1` INT, 
ADD `path_level_2` INT, 
ADD `path_level_3` INT;

-- 觸發器自動維護
CREATE TRIGGER `tg_path_split` BEFORE INSERT ON `Survey_QuestionColumns`
FOR EACH ROW BEGIN
    SET NEW.`path_level_1` = CAST(SUBSTRING_INDEX(NEW.`ColumnPath`, '|', 1) AS UNSIGNED);
    SET NEW.`path_level_2` = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.`ColumnPath`, '|', 2), '|', -1) AS UNSIGNED);
    SET NEW.`path_level_3` = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.`ColumnPath`, '|', 3), '|', -1) AS UNSIGNED);
END;

-- 查詢變為極簡
SELECT * FROM `Survey_QuestionColumns` WHERE `path_level_3` = 3;

「在數據庫裡做字符串拆分,通常是設計不當的信號。」—— 盡可能在寫入時規範化,或下推到應用層。

copyright © 萬盛學電腦網 all rights reserved