-- 直接拼接: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 動態 SQL | EXEC('SELECT ... IN ('+@Ids+')') | ❌ 每次重編譯 | ✅ | 無 | 中 | ⭐⭐ 臨時方案 |
| 3. 逐個參數化 | IN (@p1,@p2,@p3...) 程式端拼 SQL 參數名 | ✅ | ✅ | 2100 參數 | 低 | ⭐⭐⭐⭐ <1000 筆首選 |
| 4. 臨時表/表值參數 (TVP) | 建 #Temp 或 TVP,JOIN/EXISTS 查詢 | ✅ | ✅ | 無上限 | 低(建表開銷) | ⭐⭐⭐⭐⭐ 千筆以上最強 |
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...
}
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>,無需建臨時表、解析字串,效能最優。
通配符放參數值:@Pattern = 'abc%' → WHERE Name LIKE @Pattern。避免 在 SQL 拼 WHERE Name LIKE '%' + @Val + '%'(導致掃描)。