SqlServer参数化查询之wherein和like实现详解

2023年6月21日发(作者：)

SqlServer参数化查询之wherein和like实现详解⾝为⼀名⼩⼩的程序猿，在⽇常开发中不可以避免的要和where in和like打交道，在⼤多数情况下我们传的参数不多简单做下单引号、敏感字符转义之后就直接拼进了SQL，执⾏查询，搞定。若有⼀天你不可避免的需要提⾼SQL的查询性能，需要⼀次性where in ⼏百、上千、甚⾄上万条数据时，参数化查询将是必然进⾏的选择。然⽽如何实现where in和like的参数化查询，是个让不少⼈头疼的问题。

⾸先说⼀下我们常⽤的办法，直接拼SQL实现，⼀般情况下都能满⾜需要

复制代码 代码如下:string userIds = "1,2,3,4";

using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

dText = ("select * from Users(nolock) where UserID in({0})", userIds);

eNonQuery();

}

需要参数化查询时进⾏的尝试，很显然如下这样执⾏SQL会报错错误

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

dText = "select * from Users(nolock) where UserID in(@UserID)";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

很显然这样会报错误：在将 varchar 值 '1,2,3,4' 转换成数据类型 int 时失败，因为参数类型为字符串，where in时会把@UserID当做⼀个字符串来处理，相当于实际执⾏了如下语句

复制代码 代码如下:select * from Users(nolock) where UserID in('1,2,3,4')

若执⾏的语句为字符串类型的，SQL执⾏不会报错，当然也不会查询出任何结果

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

dText = "select * from Users(nolock) where UserName in(@UserName)";

(new SqlParameter("@UserName", r, -1) { Value = "'john','dudu','rabbit'" });

eNonQuery();

}

这样不会抱任何错误，也查不出想要的结果，因为这个@UserName被当做⼀个字符串来处理，实际相当于执⾏如下语句

复制代码 代码如下:select * from Users(nolock) where UserName in('''john'',''dudu'',''rabbit''')

由此相信⼤家对于为何简单的where in 传参⽆法得到正确的结果知道为什么了吧，下⾯我们来看⼀看如何实现正确的参数化执⾏where in，为了真正实现参数化where in 传参，很多淫才想到了各种替代⽅案 ⽅案1，使⽤CHARINDEX或like ⽅法实现参数化查询，毫⽆疑问，这种⽅法成功了，⽽且成功的复⽤了查询计划，但同时也彻底的让查询索引失效(在此不探讨索引话题)，造成的后果是全表扫描，如果表⾥数据量很⼤，百万级、千万级甚⾄更多，这样的写法将造成灾难性后果；如果数据量⽐较⼩、只想借助参数化实现防⽌SQL注⼊的话这样写也⽆可厚⾮，还是得看具体需求。(不推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//使⽤CHARINDEX，实现参数化查询，可以复⽤查询计划，同时会使索引失效

dText = "select * from Users(nolock) where CHARINDEX(','+ltrim(str(UserID))+',',','+@UserID+',')>0";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//使⽤like，实现参数化查询，可以复⽤查询计划，同时会使索引失效

dText = "select * from Users(nolock) where ','+@UserID+',' like '%,'+ltrim(str(UserID))+',%' ";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

⽅案2 使⽤exec动态执⾏SQL，这样的写法毫⽆疑问是很成功的，⽽且代码也⽐较优雅，也起到了防⽌SQL注⼊的作⽤，看上去很完美，不过这种写法和直接拼SQL执⾏没啥实质性的区别，查询计划没有得到复⽤，对于性能提升没任何帮助，颇有种脱了裤⼦放屁的感觉，但也不失为⼀种解决⽅案。(不推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//使⽤exec动态执⾏SQL

　　//实际执⾏的查询计划为(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)

　　//不是预期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')')

dText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

⽅案3 为where in的每⼀个参数⽣成⼀个参数，写法上⽐较⿇烦些，传输的参数个数有限制，最多2100个，可以根据需要使⽤此⽅案(推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//为每⼀条数据添加⼀个参数

dText = "select * from Users(nolock) where UserID in (@UserID1,@UserId2,@UserID3,@UserID4)";

ge(

new SqlParameter[]{

new SqlParameter("@UserID1", ) { Value = 1}, new SqlParameter("@UserID2", ) { Value = 2},

new SqlParameter("@UserID3", ) { Value = 3},

new SqlParameter("@UserID4", ) { Value = 4}

});

eNonQuery();

}

⽅案4 使⽤临时表实现(也可以使⽤表变量性能上可能会更加好些)，写法实现上⽐较繁琐些，可以根据需要写个通⽤的wherein临时表查询的⽅法，以供不时之需，个⼈⽐较推崇这种写法，能够使查询计划得到复⽤⽽且对索引也能有效的利⽤，不过由于需要创建临时表，会带来额外的IO开销，若查询频率很⾼，每次的数据不多时还是建议使⽤⽅案3，若查询数据条数较多，尤其是上千条甚⾄上万条时，强烈建议使⽤此⽅案，可以带来巨⼤的性能提升(强烈推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = 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_=)

drop table #Temp_Table";

dText = sql;

(new SqlParameter("@Temp_Array", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

like参数化查询

like查询根据个⼈习惯将通配符写到参数值中或在SQL拼接都可，两种⽅法执⾏效果⼀样，在此不在详述

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//将 % 写到参数值中

dText = "select * from Users(nolock) where UserName like @UserName";

(new SqlParameter("@UserName", r, 200) { Value = "rabbit%" });

eNonQuery();

}

using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand(); tion = conn;

//SQL中拼接 %

dText = "select * from Users(nolock) where UserName like @UserName+'%'";

(new SqlParameter("@UserName", r, 200) { Value = "rabbit%" });

eNonQuery();

}

看到Tom.汤和蚊⼦额的评论 补充了下xml传参和tvp传参，并对6种⽅案做了个简单总结

Sql Server参数化查询之where in和like实现之xml和DataTable传参

此⽂章属懒惰的肥兔原创

2023年6月21日发(作者：)

SqlServer参数化查询之wherein和like实现详解⾝为⼀名⼩⼩的程序猿，在⽇常开发中不可以避免的要和where in和like打交道，在⼤多数情况下我们传的参数不多简单做下单引号、敏感字符转义之后就直接拼进了SQL，执⾏查询，搞定。若有⼀天你不可避免的需要提⾼SQL的查询性能，需要⼀次性where in ⼏百、上千、甚⾄上万条数据时，参数化查询将是必然进⾏的选择。然⽽如何实现where in和like的参数化查询，是个让不少⼈头疼的问题。

⾸先说⼀下我们常⽤的办法，直接拼SQL实现，⼀般情况下都能满⾜需要

复制代码 代码如下:string userIds = "1,2,3,4";

using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

dText = ("select * from Users(nolock) where UserID in({0})", userIds);

eNonQuery();

}

需要参数化查询时进⾏的尝试，很显然如下这样执⾏SQL会报错错误

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

dText = "select * from Users(nolock) where UserID in(@UserID)";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

很显然这样会报错误：在将 varchar 值 '1,2,3,4' 转换成数据类型 int 时失败，因为参数类型为字符串，where in时会把@UserID当做⼀个字符串来处理，相当于实际执⾏了如下语句

复制代码 代码如下:select * from Users(nolock) where UserID in('1,2,3,4')

若执⾏的语句为字符串类型的，SQL执⾏不会报错，当然也不会查询出任何结果

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

dText = "select * from Users(nolock) where UserName in(@UserName)";

(new SqlParameter("@UserName", r, -1) { Value = "'john','dudu','rabbit'" });

eNonQuery();

}

这样不会抱任何错误，也查不出想要的结果，因为这个@UserName被当做⼀个字符串来处理，实际相当于执⾏如下语句

复制代码 代码如下:select * from Users(nolock) where UserName in('''john'',''dudu'',''rabbit''')

由此相信⼤家对于为何简单的where in 传参⽆法得到正确的结果知道为什么了吧，下⾯我们来看⼀看如何实现正确的参数化执⾏where in，为了真正实现参数化where in 传参，很多淫才想到了各种替代⽅案 ⽅案1，使⽤CHARINDEX或like ⽅法实现参数化查询，毫⽆疑问，这种⽅法成功了，⽽且成功的复⽤了查询计划，但同时也彻底的让查询索引失效(在此不探讨索引话题)，造成的后果是全表扫描，如果表⾥数据量很⼤，百万级、千万级甚⾄更多，这样的写法将造成灾难性后果；如果数据量⽐较⼩、只想借助参数化实现防⽌SQL注⼊的话这样写也⽆可厚⾮，还是得看具体需求。(不推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//使⽤CHARINDEX，实现参数化查询，可以复⽤查询计划，同时会使索引失效

dText = "select * from Users(nolock) where CHARINDEX(','+ltrim(str(UserID))+',',','+@UserID+',')>0";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//使⽤like，实现参数化查询，可以复⽤查询计划，同时会使索引失效

dText = "select * from Users(nolock) where ','+@UserID+',' like '%,'+ltrim(str(UserID))+',%' ";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

⽅案2 使⽤exec动态执⾏SQL，这样的写法毫⽆疑问是很成功的，⽽且代码也⽐较优雅，也起到了防⽌SQL注⼊的作⽤，看上去很完美，不过这种写法和直接拼SQL执⾏没啥实质性的区别，查询计划没有得到复⽤，对于性能提升没任何帮助，颇有种脱了裤⼦放屁的感觉，但也不失为⼀种解决⽅案。(不推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//使⽤exec动态执⾏SQL

　　//实际执⾏的查询计划为(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)

　　//不是预期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')')

dText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";

(new SqlParameter("@UserID", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

⽅案3 为where in的每⼀个参数⽣成⼀个参数，写法上⽐较⿇烦些，传输的参数个数有限制，最多2100个，可以根据需要使⽤此⽅案(推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//为每⼀条数据添加⼀个参数

dText = "select * from Users(nolock) where UserID in (@UserID1,@UserId2,@UserID3,@UserID4)";

ge(

new SqlParameter[]{

new SqlParameter("@UserID1", ) { Value = 1}, new SqlParameter("@UserID2", ) { Value = 2},

new SqlParameter("@UserID3", ) { Value = 3},

new SqlParameter("@UserID4", ) { Value = 4}

});

eNonQuery();

}

⽅案4 使⽤临时表实现(也可以使⽤表变量性能上可能会更加好些)，写法实现上⽐较繁琐些，可以根据需要写个通⽤的wherein临时表查询的⽅法，以供不时之需，个⼈⽐较推崇这种写法，能够使查询计划得到复⽤⽽且对索引也能有效的利⽤，不过由于需要创建临时表，会带来额外的IO开销，若查询频率很⾼，每次的数据不多时还是建议使⽤⽅案3，若查询数据条数较多，尤其是上千条甚⾄上万条时，强烈建议使⽤此⽅案，可以带来巨⼤的性能提升(强烈推荐)

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = 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_=)

drop table #Temp_Table";

dText = sql;

(new SqlParameter("@Temp_Array", r, -1) { Value = "1,2,3,4" });

eNonQuery();

}

like参数化查询

like查询根据个⼈习惯将通配符写到参数值中或在SQL拼接都可，两种⽅法执⾏效果⼀样，在此不在详述

复制代码 代码如下:using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand();

tion = conn;

//将 % 写到参数值中

dText = "select * from Users(nolock) where UserName like @UserName";

(new SqlParameter("@UserName", r, 200) { Value = "rabbit%" });

eNonQuery();

}

using (SqlConnection conn = new SqlConnection(connectionString))

{

();

SqlCommand comm = new SqlCommand(); tion = conn;

//SQL中拼接 %

dText = "select * from Users(nolock) where UserName like @UserName+'%'";

(new SqlParameter("@UserName", r, 200) { Value = "rabbit%" });

eNonQuery();

}

看到Tom.汤和蚊⼦额的评论 补充了下xml传参和tvp传参，并对6种⽅案做了个简单总结

Sql Server参数化查询之where in和like实现之xml和DataTable传参

此⽂章属懒惰的肥兔原创