萬盛學電腦網

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

SQL Server 參數化查詢大數據實戰:IN 與 LIKE 的四大方案深度對比與最佳實踐

問題:字串拼接 IN 列表會炸效能與安全

-- 直接拼接:SQL 注入風險、計劃無法複用、大量參數超長度限制
string sql = "SELECT * FROM Users WHERE UserID IN (" + userIds + ")";

需求:單次 IN 幾百~上萬 ID,防注入、復用計劃、走索引。

方案對比表

方案原理計劃復用索引可用參數上限IO/CPU推薦度
1. CHARINDEX/LIKE 模糊匹配WHERE ','+@Ids+',' LIKE '%,'+CAST(Id AS VARCHAR)+',%'✅❌ 全掃描無極高⭐ 僅小表/防注入
2. EXEC 動態 SQLEXEC('SELECT ... IN ('+@Ids+')')❌ 每次重編譯✅無中⭐⭐ 臨時方案
3. 逐個參數化IN (@p1,@p2,@p3...) 程式端拼 SQL 參數名✅✅2100 參數低⭐⭐⭐⭐ <1000 筆首選
4. 臨時表/表值參數 (TVP)建 #Temp 或 TVP,JOIN/EXISTS 查詢✅✅無上限低(建表開銷)⭐⭐⭐⭐⭐ 千筆以上最強

方案 3 程式碼範例(C#)

var ids = new[] {1,2,3,4};
var paramNames = ids.Select((_, i) => "@Id" + i).ToArray();
var cmdText = "SELECT * FROM Users WHERE UserID IN (" + string.Join(",", paramNames) + ")";
using (var cmd = new SqlCommand(cmdText, conn)) {
  for (int i = 0; i < ids.Length; i++)
    cmd.Parameters.AddWithValue(paramNames[i], ids[i]);
  // Execute...
}

方案 4 臨時表完整範例(T-SQL)

DECLARE @IdList VARCHAR(MAX) = '1,2,3,4,5...10000';
CREATE TABLE #TmpIds (Id INT PRIMARY KEY); -- PK 利於 JOIN
DECLARE @Pos INT, @Val VARCHAR(20);
WHILE LEN(@IdList) > 0 BEGIN
  SET @Pos = CHARINDEX(',', @IdList);
  IF @Pos = 0 BEGIN SET @Val = @IdList; SET @IdList = ''; END
  ELSE BEGIN SET @Val = LEFT(@IdList, @Pos-1); SET @IdList = RIGHT(@IdList, LEN(@IdList)-@Pos); END
  INSERT #TmpIds(Id) VALUES (CAST(@Val AS INT));
END
SELECT u.* FROM Users u WHERE EXISTS (SELECT 1 FROM #TmpIds t WHERE t.Id = u.UserID);
DROP TABLE #TmpIds;

進階:SQL 2008+ 可用表值參數 (TVP),定義 CREATE TYPE IdList AS TABLE (Id INT PRIMARY KEY),C# 傳 DataTable/IEnumerable<SqlDataRecord>,無需建臨時表、解析字串,效能最優。

LIKE 參數化

通配符放參數值:@Pattern = 'abc%' → WHERE Name LIKE @Pattern。避免 在 SQL 拼 WHERE Name LIKE '%' + @Val + '%'(導致掃描)。

總結選型指南

  • < 500 筆、低頻:方案 3 逐個參數,簡單無依賴;
  • > 1000 筆、高頻、大表:方案 4 TVP/臨時表,計劃穩定、索引友善、無參數上限;
  • 絕對禁止:方案 1(全掃描殺大表)、方案 2(計劃爆炸)。
copyright © 萬盛學電腦網 all rights reserved