萬盛學電腦網

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

SQL Server 2005 自定義字串分割函數:WHILE 迴圈實現 SPLIT 邏輯與現代替代方案

SQL Server 2016 前無內建 `STRING_SPLIT`,開發者常自寫分割函數。本文解析原文 `WHILE` 迴圈標量函數邏輯,指出**效能陷阱 (RBAR 逐行處理)**,並提供 **內建 `STRING_SPLIT`、JSON 解析、XML 解析、CLR、數字表** 五大現代高效替代方案。

一、 原始函數邏輯分析

ALTER FUNCTION dbo.core_split 
(
    @str VARCHAR(100), 
    @split CHAR(1), 
    @index INT
)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @count INT = 0, @s VARCHAR(10) = '';
    WHILE (CHARINDEX(@split, @str) <> 0)
    BEGIN
        IF @count = @index 
            SET @s = SUBSTRING(@str, 1, CHARINDEX(@split, @str) - 1);
        SET @str = STUFF(@str, 1, CHARINDEX(@split, @str), ''); -- 移除已處理段
        SET @count += 1;
    END
    IF CHARINDEX(@split, @str) = 0 SET @s = @str; -- 處理最後一段
    RETURN @s;
END

關鍵缺陷

  • 標量函數 (Scalar UDF) 慘烈效能:查詢中每行呼叫一次函數 → **單執行緒串行**、無法內聯、嚴重拖慢查詢 (典型 RBAR - Row By Agonizing Row)。
  • 僅回傳單一索引值** : 使用需多次呼叫函數獲取不同位置元素,極度低效。
  • 長度限制** : `@str VARCHAR(100)`、回傳 `VARCHAR(10)`,實際場景遠不夠。
  • 邊界邏輯脆弱** : 連續分隔符、前導/尾隨分隔符、空字串處理未定義。

二、 現代高效替代方案 (按版本遞增)

1. STRING_SPLIT (SQL Server 2016+ / Azure SQL) —— 標準首選

-- 回傳資料表值函數 (TVF),可直接 JOIN / CROSS APPLY
SELECT value FROM STRING_SPLIT('aa,dd,cc,rr,fff', ','); 
-- 結果:每行一個元素 (順序不保證,SQL 2022+ 支援 enable_ordinal=1 保序)

-- 實戰:將逗號分隔字串轉為行集合
SELECT s.value 
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.CsvColumn, ',') s;
  • **原生 C++ 實現,極快**,集合操作,可並行。
  • **注意**:預設**不保證順序** (SQL 2022+ 可 `STRING_SPLIT(..., ',', 1)` 獲取 `ordinal` 欄位排序)。
  • **不修剪空白** `'a, b'` → `'a'`, `' b'`,需 `TRIM()` 處理。

2. OPENJSON (SQL Server 2016+) —— 保序、支援型別、處理雙引號

-- 將逗號分隔字串包裝為 JSON 陣列
SELECT [value] 
FROM OPENJSON('["' + REPLACE('aa,dd,cc,rr,fff', ',', '","') + '"]');
-- 結果:保證順序 (含 key=索引)、自動處理引號轉義、回傳型別 (字串/數值/布林/NULL/物件/陣列)
  • 需有效 JSON 格式,字串含雙引號/反斜線需轉義 (或用 `JSON_MODIFY` 構建)。
  • **強烈推薦** 處理結構化分割需求。

3. XML 解析 (SQL Server 2005+) —— 經典相容方案

DECLARE @xml XML = N'';
SELECT x.value('(./text())[1]', 'VARCHAR(100)') 
FROM @xml.nodes('/r') AS T(x);
  • 利用 XML 節點分割,CDATA 區塊安全處理特殊字元。
  • 效能中等,大字串/高併發較慢 (XML 解析開銷大)。

4. 數字表 / Tally Table (全版本通用、集合思維)

-- 建立數字表 (一次性建立,永久使用)
CREATE TABLE dbo.Tally (N INT PRIMARY KEY);
INSERT INTO dbo.Tally (N) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b;

-- 分割查詢 (單次掃描,集合操作)
DECLARE @str VARCHAR(8000) = 'aa,dd,cc,rr,fff', @delim CHAR(1) = ',';
SELECT 
    SUBSTRING(@str, N, CHARINDEX(@delim, @str + @delim, N) - N) AS Token
FROM dbo.Tally
WHERE N <= LEN(@str) + 1
  AND (N = 1 OR SUBSTRING(@str, N-1, 1) = @delim)
ORDER BY N;
  • **集合操作、無迴圈、可並行、效能極佳**、全版本通用 (2000+)。
  • DBA 必備工具表,亦用於日期序列、報表填補等。

5. CLR 使用者定義函數 (C# 實現) —— 極致效能與功能

  • C# `string.Split` / `Regex.Split` 封裝為 `SqlFunction` (Table-Valued),註冊至 SQL Server (`CREATE ASSEMBLY` / `CREATE FUNCTION`)。
  • 支援正則分割、複雜邏輯、高效能、狀態管理。
  • 需啟用 `clr enabled`、信任資料庫設定 (`TRUSTWORTHY` / 非對稱金鑰簽署),運維門檻較高。

三、 方案選型決策表

場景推薦方案關鍵優勢
SQL 2016+、單純分割、不需順序STRING_SPLIT原生、最快、語法最簡
SQL 2016+、需保序/型別/處理引號OPENJSON標準 JSON、保序、型別豐富
SQL 2005-2014、高效能分割Tally Table / Numbers Table集合操作、無迴圈、全版本通用
極複雜分割邏輯 (正則、巢狀、多字元分隔符)CLR TVFC# 生態全威力、效能最強
舊系統維護、極小資料量、臨時腳本原 WHILE 函數 / XML相容性、快速寫完

四、 實戰模式:分割後聚合/查詢

-- 查找含特定標籤的文章 (標籤存為 CSV)
SELECT a.* FROM Article a
WHERE EXISTS (
    SELECT 1 FROM STRING_SPLIT(a.Tags, ',') s WHERE TRIM(s.value) = 'SQL'
);

結語

**標量函數 + WHILE 迴圈分割是效能殺手**,嚴禁用於生產查詢核心路徑。**SQL 2016+ 直接用 `STRING_SPLIT` / `OPENJSON`**;舊版本建立 **永久 Tally Table** 進行集合式分割。將分割邏輯下推至資料庫層 (TVF/CROSS APPLY) 而非應用層迴圈,是關係型思維的體現,亦是高效能的前提。

copyright © 萬盛學電腦網 all rights reserved