字段 ColumnPath 存儲路徑字符串 '1|3|4|6',需查詢「第 3 段等於 '3'」的記錄。
-- 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';
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';
-- 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、無需輔助表 | 遞歸開銷大、深度受限 | 臨時分析、中小表 |
| 數字表 JOIN | 5.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;
「在數據庫裡做字符串拆分,通常是設計不當的信號。」—— 盡可能在寫入時規範化,或下推到應用層。