太粗太深了用力点视频,爽?躁多水?快?深点车上

主頁(yè) > 知識(shí)庫(kù) > SqlServer參數(shù)化查詢之where in和like實(shí)現(xiàn)詳解

SqlServer參數(shù)化查詢之where in和like實(shí)現(xiàn)詳解

身為一名小小的程序猿，在日常開(kāi)發(fā)中不可以避免的要和where in和like打交道，在大多數(shù)情況下我們傳的參數(shù)不多簡(jiǎn)單做下單引號(hào)、敏感字符轉(zhuǎn)義之后就直接拼進(jìn)了SQL，執(zhí)行查詢，搞定。若有一天你不可避免的需要提高SQL的查詢性能，需要一次性where in 幾百、上千、甚至上萬(wàn)條數(shù)據(jù)時(shí)，參數(shù)化查詢將是必然進(jìn)行的選擇。然而如何實(shí)現(xiàn)where in和like的參數(shù)化查詢，是個(gè)讓不少人頭疼的問(wèn)題。

where in 的參數(shù)化查詢實(shí)現(xiàn)

首先說(shuō)一下我們常用的辦法，直接拼SQL實(shí)現(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ù)化查詢時(shí)進(jìn)行的嘗試，很顯然如下這樣執(zhí)行SQL會(huì)報(bào)錯(cuò)錯(cuò)誤

復(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(); 
} 

很顯然這樣會(huì)報(bào)錯(cuò)誤：在將 varchar 值 '1,2,3,4' 轉(zhuǎn)換成數(shù)據(jù)類型 int 時(shí)失敗，因?yàn)閰?shù)類型為字符串，where in時(shí)會(huì)把@UserID當(dāng)做一個(gè)字符串來(lái)處理，相當(dāng)于實(shí)際執(zhí)行了如下語(yǔ)句

復(fù)制代碼代碼如下:

select * from Users(nolock) where UserID in('1,2,3,4')

若執(zhí)行的語(yǔ)句為字符串類型的，SQL執(zhí)行不會(huì)報(bào)錯(cuò)，當(dāng)然也不會(huì)查詢出任何結(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(); 
} 

這樣不會(huì)抱任何錯(cuò)誤，也查不出想要的結(jié)果，因?yàn)檫@個(gè)@UserName被當(dāng)做一個(gè)字符串來(lái)處理，實(shí)際相當(dāng)于執(zhí)行如下語(yǔ)句

復(fù)制代碼代碼如下:

select * from Users(nolock) where UserName in('''john'',''dudu'',''rabbit''')

由此相信大家對(duì)于為何簡(jiǎn)單的where in 傳參無(wú)法得到正確的結(jié)果知道為什么了吧，下面我們來(lái)看一看如何實(shí)現(xiàn)正確的參數(shù)化執(zhí)行where in，為了真正實(shí)現(xiàn)參數(shù)化where in 傳參，很多淫才想到了各種替代方案

方案1，使用CHARINDEX或like 方法實(shí)現(xiàn)參數(shù)化查詢，毫無(wú)疑問(wèn)，這種方法成功了，而且成功的復(fù)用了查詢計(jì)劃，但同時(shí)也徹底的讓查詢索引失效(在此不探討索引話題)，造成的后果是全表掃描，如果表里數(shù)據(jù)量很大，百萬(wàn)級(jí)、千萬(wàn)級(jí)甚至更多，這樣的寫法將造成災(zāi)難性后果；如果數(shù)據(jù)量比較小、只想借助參數(shù)化實(shí)現(xiàn)防止SQL注入的話這樣寫也無(wú)可厚非，還是得看具體需求。(不推薦)

復(fù)制代碼代碼如下:

 
using (SqlConnection conn = new SqlConnection(connectionString)) 
{ 
conn.Open(); 
SqlCommand comm = new SqlCommand(); 
comm.Connection = conn; 
//使用CHARINDEX，實(shí)現(xiàn)參數(shù)化查詢，可以復(fù)用查詢計(jì)劃，同時(shí)會(huì)使索引失效 
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，實(shí)現(xiàn)參數(shù)化查詢，可以復(fù)用查詢計(jì)劃，同時(shí)會(huì)使索引失效 
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動(dòng)態(tài)執(zhí)行SQL，這樣的寫法毫無(wú)疑問(wèn)是很成功的，而且代碼也比較優(yōu)雅，也起到了防止SQL注入的作用，看上去很完美，不過(guò)這種寫法和直接拼SQL執(zhí)行沒(méi)啥實(shí)質(zhì)性的區(qū)別，查詢計(jì)劃沒(méi)有得到復(fù)用，對(duì)于性能提升沒(méi)任何幫助，頗有種脫了褲子放屁的感覺(jué)，但也不失為一種解決方案。(不推薦)

復(fù)制代碼代碼如下:

 
using (SqlConnection conn = new SqlConnection(connectionString)) 
{ 
conn.Open(); 
SqlCommand comm = new SqlCommand(); 
comm.Connection = conn; 
//使用exec動(dòng)態(tài)執(zhí)行SQL 
　　//實(shí)際執(zhí)行的查詢計(jì)劃為(@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的每一個(gè)參數(shù)生成一個(gè)參數(shù)，寫法上比較麻煩些，傳輸?shù)膮?shù)個(gè)數(shù)有限制，最多2100個(gè)，可以根據(jù)需要使用此方案(推薦)

復(fù)制代碼代碼如下:

 
using (SqlConnection conn = new SqlConnection(connectionString)) 
{ 
conn.Open(); 
SqlCommand comm = new SqlCommand(); 
comm.Connection = conn; 
//為每一條數(shù)據(jù)添加一個(gè)參數(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 使用臨時(shí)表實(shí)現(xiàn)(也可以使用表變量性能上可能會(huì)更加好些)，寫法實(shí)現(xiàn)上比較繁瑣些，可以根據(jù)需要寫個(gè)通用的where in臨時(shí)表查詢的方法，以供不時(shí)之需，個(gè)人比較推崇這種寫法，能夠使查詢計(jì)劃得到復(fù)用而且對(duì)索引也能有效的利用，不過(guò)由于需要?jiǎng)?chuàng)建臨時(shí)表，會(huì)帶來(lái)額外的IO開(kāi)銷，若查詢頻率很高，每次的數(shù)據(jù)不多時(shí)還是建議使用方案3，若查詢數(shù)據(jù)條數(shù)較多，尤其是上千條甚至上萬(wàn)條時(shí)，強(qiáng)烈建議使用此方案，可以帶來(lái)巨大的性能提升(強(qiáng)烈推薦)

復(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ù)個(gè)人習(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.湯和蚊子額的評(píng)論補(bǔ)充了下xml傳參和tvp傳參，并對(duì)6種方案做了個(gè)簡(jiǎn)單總結(jié)

Sql Server參數(shù)化查詢之where in和like實(shí)現(xiàn)之xml和DataTable傳參

此文章屬懶惰的肥兔原創(chuàng)

您可能感興趣的文章:

SQLServer中使用擴(kuò)展事件獲取Session級(jí)別的等待信息及SQLServer 2016中Session級(jí)別等待信息的增強(qiáng)
sqlserver 模糊查詢常用方法
SqlServer使用 case when 解決多條件模糊查詢問(wèn)題
SqlServer中模糊查詢對(duì)于特殊字符的處理方法
MSSQL Server 查詢優(yōu)化方法整理
sqlserver 中charindex/patindex/like 的比較
SqlServer參數(shù)化查詢之where in和like實(shí)現(xiàn)之xml和DataTable傳參介紹
SqlServer2016模糊匹配的三種方式及效率問(wèn)題簡(jiǎn)析

標(biāo)簽：新余金昌河源宜春貸款群呼黃山濟(jì)寧中衛(wèi)

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SqlServer參數(shù)化查詢之where in和like實(shí)現(xiàn)詳解》，本文關(guān)鍵詞 SqlServer,參數(shù),化,查詢,之,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。