您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 參數化查詢實戰：大量 WHERE IN 與 LIKE 的高效實作方案對比

編輯：關於SqlServer

面對數百上千個 WHERE IN 參數或 LIKE 模糊查詢，直接拼接 SQL 雖簡單但無法重用執行計畫、有注入風險。本文深度對比 4 種 WHERE IN 參數化方案與 2 種 LIKE 參數化寫法，從執行計畫重用、索引利用、參數上限、IO 開銷等維度分析優劣，給出明確選型建議。

問題背景：直接拼接 vs 參數化

-- 直接拼接 (簡單但有風險)
string userIds = "1,2,3,4";
string sql = $"SELECT * FROM Users WHERE UserID IN ({userIds})";

缺點：
❌ 每次參數不同產生新計畫 → 計畫快取膨脹
❌ SQL 注入風險 (若參數來自用戶輸入)
❌ 參數過多超過批次大小限制

WHERE IN 參數化 4 大方案深度對比

方案 1：CHARINDEX / LIKE 模擬 (❌ 不推薦)

-- CHARINDEX 版
SELECT * FROM Users WHERE CHARINDEX(','+LTRIM(STR(UserID))+',', ','+@UserIDs+',') > 0;

-- LIKE 版
SELECT * FROM Users WHERE ','+@UserIDs+',' LIKE '%,'+LTRIM(STR(UserID))+',%';

✅ 參數化成功、計畫重用
❌ 導致索引失效 → 全表掃描，百萬級資料災難
❌ 字串運算 CPU 開銷大

方案 2：EXEC 動態 SQL (❌ 不推薦)

EXEC('SELECT * FROM Users WHERE UserID IN (' + @UserIDs + ')');

✅ 語法正確、索引可用
❌ 計畫不重用 (每次參數不同產生新計畫)
❌ 同樣有注入風險 (若 @UserIDs 未驗證)
❌ 「脫褲子放屁」——失去參數化核心優勢

方案 3：逐參數化 (✅ 推薦，小量參數 < 2100)

-- 動態建立參數 @UserID1, @UserID2...
SELECT * FROM Users WHERE UserID IN (@UserID1, @UserID2, @UserID3, @UserID4);

✅ 完全參數化、計畫重用、索引 Seek 最佳
✅ 防注入、型別安全
⚠️ 參數上限 2100 (SQL Server 限制)
⚠️ 參數超過 ~100 時 SQL 字串極長，解析編譯開銷增大

方案 4：暫存表 / 表值參數 (✅ 強烈推薦，大量參數 > 1000)

-- 暫存表版 (單一參數傳入逗號字串，SP 內部解析填表)
CREATE PROC usp_GetUsers @UserIDs VARCHAR(MAX)
AS
BEGIN
    CREATE TABLE #Temp (UserID INT PRIMARY KEY);
    DECLARE @Pos INT, @Val VARCHAR(20);
    WHILE LEN(@UserIDs) > 0
    BEGIN
        SET @Pos = CHARINDEX(',', @UserIDs);
        IF @Pos = 0 SET @Val = @UserIDs, @UserIDs = '';
        ELSE SET @Val = LEFT(@UserIDs, @Pos-1), @UserIDs = RIGHT(@UserIDs, LEN(@UserIDs)-@Pos);
        INSERT INTO #Temp VALUES (CAST(@Val AS INT));
    END
    SELECT u.* FROM Users u INNER JOIN #Temp t ON u.UserID = t.UserID;
    DROP TABLE #Temp;
END

✅ 單一參數、計畫重用、索引 Join 高效
✅ 無參數數量上限 (受限於暫存表容量)
✅ 適合上千、上萬參數場景
⚠️ 額外建立/填充暫存表 IO 開銷 (高頻小量查詢不划算)

方案選型決策樹

參數量級	查詢頻率	推薦方案	備註
< 50	高	方案 3 (逐參數)	最低延遲、零額外 IO
50 ~ 2000	中/低	方案 3 或 4	視開發便利度選擇
> 2000	任何	方案 4 (暫存表/TVP)	唯一可行方案
任何	極高頻 (千 QPS+)	方案 3 + 計畫引導/快取	避免暫存表建立開銷

LIKE 參數化：兩種等效寫法

-- 寫法 1：萬用字元放在參數值
cmd.CommandText = "SELECT * FROM Users WHERE UserName LIKE @Name";
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 200).Value = "rabbit%";

-- 寫法 2：SQL 中串接萬用字元
cmd.CommandText = "SELECT * FROM Users WHERE UserName LIKE @Name + '%'";
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 200).Value = "rabbit";

兩者執行計畫、效能完全相同，選擇團隊慣例即可。注意：前置萬用字元 (%rabbit%) 會導致索引掃描，後置 (rabbit%) 可索引 Seek。

進階：表值參數 (TVP) 替代暫存表 (SQL 2008+)

-- 1. 建立使用者定義資料表類型
CREATE TYPE dbo.UserIDTable AS TABLE (UserID INT PRIMARY KEY);

-- 2. SP 參數使用 TVP
CREATE PROC usp_GetUsers_TVP @UserIDs dbo.UserIDTable READONLY
AS
SELECT u.* FROM Users u INNER JOIN @UserIDs t ON u.UserID = t.UserID;

-- 3. C# 呼叫
var dt = new DataTable(); dt.Columns.Add("UserID", typeof(int));
foreach(var id in ids) dt.Rows.Add(id);
cmd.Parameters.AddWithValue("@UserIDs", dt);  // 自動推斷 TVP

TVP 優勢：
✅ 強型別、無暫存表建立/刪除開銷
✅ 統計資訊自動維護、計畫穩定
✅ 僅讀、無鎖衝突
→ 大量參數參數化的現代標準解法。