身為一名小小的程序猿，在日常開發(fā)中不可以避免的要和where in和like打交道，在大多數(shù)情況下我們傳的參數(shù)不多簡單做下單引號、敏感字符轉(zhuǎn)義之后就直接拼進(jìn)了SQL，執(zhí)行查詢，搞定。若有一天你不可避免的需要提高SQL的查詢性能，需要一次性where in 幾百、上千、甚至上萬條數(shù)據(jù)時，參數(shù)化查詢將是必然進(jìn)行的選擇。然而如何實現(xiàn)where in和like的參數(shù)化查詢，是個讓不少人頭疼的問題。
where in 的參數(shù)化查詢實現(xiàn)
首先說一下我們常用的辦法，直接拼SQL實現(xiàn)，一般情況下都能滿足需要
復(fù)制代碼代碼如下:
string userIds = "1,2,3,4";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = string.Format("select * from Users(nolock) where UserID in({0})", userIds);
comm.ExecuteNonQuery();
}
需要參數(shù)化查詢時進(jìn)行的嘗試，很顯然如下這樣執(zhí)行SQL會報錯錯誤
復(fù)制代碼代碼如下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from Users(nolock) where UserID in(@UserID)";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
很顯然這樣會報錯誤：在將 varchar 值 '1,2,3,4' 轉(zhuǎn)換成數(shù)據(jù)類型 int 時失敗，因為參數(shù)類型為字符串，where in時會把@UserID當(dāng)做一個字符串來處理，相當(dāng)于實際執(zhí)行了如下語句
復(fù)制代碼代碼如下:
select * from Users(nolock) where UserID in('1,2,3,4')
若執(zhí)行的語句為字符串類型的，SQL執(zhí)行不會報錯，當(dāng)然也不會查詢出任何結(jié)果
復(fù)制代碼代碼如下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from Users(nolock) where UserName in(@UserName)";
comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, -1) { Value = "'john','dudu','rabbit'" });
comm.ExecuteNonQuery();
}
這樣不會抱任何錯誤，也查不出想要的結(jié)果，因為這個@UserName被當(dāng)做一個字符串來處理，實際相當(dāng)于執(zhí)行如下語句
復(fù)制代碼代碼如下:
select * from Users(nolock) where UserName in('''john'',''dudu'',''rabbit''')
由此相信大家對于為何簡單的where in 傳參無法得到正確的結(jié)果知道為什么了吧，下面我們來看一看如何實現(xiàn)正確的參數(shù)化執(zhí)行where in，為了真正實現(xiàn)參數(shù)化where in 傳參，很多淫才想到了各種替代方案
方案1，使用CHARINDEX或like 方法實現(xiàn)參數(shù)化查詢，毫無疑問，這種方法成功了，而且成功的復(fù)用了查詢計劃，但同時也徹底的讓查詢索引失效(在此不探討索引話題)，造成的后果是全表掃描，如果表里數(shù)據(jù)量很大，百萬級、千萬級甚至更多，這樣的寫法將造成災(zāi)難性后果；如果數(shù)據(jù)量比較小、只想借助參數(shù)化實現(xiàn)防止SQL注入的話這樣寫也無可厚非，還是得看具體需求。(不推薦)
復(fù)制代碼代碼如下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//使用CHARINDEX，實現(xiàn)參數(shù)化查詢，可以復(fù)用查詢計劃，同時會使索引失效
comm.CommandText = "select * from Users(nolock) where CHARINDEX(','+ltrim(str(UserID))+',',','+@UserID+',')>0";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//使用like，實現(xiàn)參數(shù)化查詢，可以復(fù)用查詢計劃，同時會使索引失效
comm.CommandText = "select * from Users(nolock) where ','+@UserID+',' like '%,'+ltrim(str(UserID))+',%' ";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
方案2 使用exec動態(tài)執(zhí)行SQL，這樣的寫法毫無疑問是很成功的，而且代碼也比較優(yōu)雅，也起到了防止SQL注入的作用，看上去很完美，不過這種寫法和直接拼SQL執(zhí)行沒啥實質(zhì)性的區(qū)別，查詢計劃沒有得到復(fù)用，對于性能提升沒任何幫助，頗有種脫了褲子放屁的感覺，但也不失為一種解決方案。(不推薦)
復(fù)制代碼代碼如下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//使用exec動態(tài)執(zhí)行SQL
//實際執(zhí)行的查詢計劃為(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)
//不是預(yù)期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')')
comm.CommandText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
方案3 為where in的每一個參數(shù)生成一個參數(shù)，寫法上比較麻煩些，傳輸?shù)膮?shù)個數(shù)有限制，最多2100個，可以根據(jù)需要使用此方案(推薦)
復(fù)制代碼代碼如下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//為每一條數(shù)據(jù)添加一個參數(shù)
comm.CommandText = "select * from Users(nolock) where UserID in (@UserID1,@UserId2,@UserID3,@UserID4)";
comm.Parameters.AddRange(
new SqlParameter[]{
new SqlParameter("@UserID1", SqlDbType.Int) { Value = 1},
new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},
new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},
new SqlParameter("@UserID4", SqlDbType.Int) { Value = 4}
});
comm.ExecuteNonQuery();
}
方案4 使用臨時表實現(xiàn)(也可以使用表變量性能上可能會更加好些)，寫法實現(xiàn)上比較繁瑣些，可以根據(jù)需要寫個通用的where in臨時表查詢的方法，以供不時之需，個人比較推崇這種寫法，能夠使查詢計劃得到復(fù)用而且對索引也能有效的利用，不過由于需要創(chuàng)建臨時表，會帶來額外的IO開銷，若查詢頻率很高，每次的數(shù)據(jù)不多時還是建議使用方案3，若查詢數(shù)據(jù)條數(shù)較多，尤其是上千條甚至上萬條時，強烈建議使用此方案，可以帶來巨大的性能提升(強烈推薦)
復(fù)制代碼代碼如下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
string sql = @"
declare @Temp_Variable varchar(max)
create table #Temp_Table(Item varchar(max))
while(LEN(@Temp_Array) > 0)
begin
if(CHARINDEX(',',@Temp_Array) = 0)
begin
set @Temp_Variable = @Temp_Array
set @Temp_Array = ''
end
else
begin
set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(',',@Temp_Array)-1)
set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1)
end
insert into #Temp_Table(Item) values(@Temp_Variable)
end
select * from Users(nolock) where exists(select 1 from #Temp_Table(nolock) where #Temp_Table.Item=Users.UserID)
drop table #Temp_Table";
comm.CommandText = sql;
comm.Parameters.Add(new SqlParameter("@Temp_Array", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
like參數(shù)化查詢
like查詢根據(jù)個人習(xí)慣將通配符寫到參數(shù)值中或在SQL拼接都可，兩種方法執(zhí)行效果一樣，在此不在詳述
復(fù)制代碼代碼如下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//將 % 寫到參數(shù)值中
comm.CommandText = "select * from Users(nolock) where UserName like @UserName";
comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 200) { Value = "rabbit%" });
comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//SQL中拼接 %
comm.CommandText = "select * from Users(nolock) where UserName like @UserName+'%'";
comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 200) { Value = "rabbit%" });
comm.ExecuteNonQuery();
}
看到Tom.湯和蚊子額的評論 補充了下xml傳參和tvp傳參，并對6種方案做了個簡單總結(jié)
Sql Server參數(shù)化查詢之where in和like實現(xiàn)之xml和DataTable傳參
此文章屬懶惰的肥兔原創(chuàng)
