当前位置：七道奇 → 文章资讯 → 数据防范 → MSSQL防范
日期：2012-06-27 05:02:00  来源：本站整理

SqlServer参数化查询之where in和like实现详解[MSSQL防范]

赞助商链接



　　本文“SqlServer参数化查询之where in和like实现详解[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
身为一名小小的程序猿,在平常开辟中不可以避免的要和where in和like打交道,在大大都情形下我们传的参数不多简单做下单引号、敏感字符转义之后就直接拼进了SQL,履行查询,搞定.若有一天你不可避免的需求提高SQL的查询性能,需求一次性where in 几百、上千、乃至上万条数据时,参数化查询将是必定举行的挑选.但是若何实现where in和like的参数化查询,是个让不少人头疼的问题.

where in 的参数化查询实现

首先说一下我们常用的办法,直接拼SQL实现,普通情形下都能满意需求
复制代码 代码以下:
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();
}

需求参数化查询时举行的尝试,很明显以下这样履行SQL会报错错误
复制代码 代码以下:
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' 转换成数据范例 int 时失利,因为参数范例为字符串,where in时会把@UserID当作一个字符串来处理,相当于实际履行了以下语句
复制代码 代码以下:
select * from Users(nolock) where UserID in('1,2,3,4')

若履行的语句为字符串范例的,SQL履行不会报错,当然也不会查询出任何后果
复制代码 代码以下:
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();
}

这样不会抱任何错误,也查不出想要的后果,因为这个@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))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//利用CHARINDEX,实现参数化查询,可以复用查询筹划,同时会使索引失效
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,实现参数化查询,可以复用查询筹划,同时会使索引失效
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动态履行SQL,这样的写法毫无疑问是很成功的,并且代码也对比文雅,也起到了避免SQL注入的作用,看上去很完善,不过这种写法和直接拼SQL履行没啥实质性的辨别,查询筹划没有得到复用,关于性能晋升没任何帮忙,颇有种脱了裤子放屁的感受,但也不失为一种办理筹划.(不举荐)
复制代码 代码以下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = 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+')')
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的每一个参数生成一个参数,写法上对比麻烦些,传输的参数个数有限制,最多2100个,可以按照需求利用此筹划(举荐)
复制代码 代码以下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//为每一条数据增添一个参数
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 利用暂时表实现(也可以利用表变量性能上大概会越发好些),写法实现上对比烦琐些,可以按照需求写个通用的where in暂时表查询的办法,以供不时之需,个人对比推崇这种写法,可以使查询筹划得到复用并且对索引也能有效的操纵,不过由于需求成立暂时表,会带来额外的IO开销,若查询频率很高,每次的数据不多时还是倡议利用筹划3,若查询数据条数较多,特别是上千条乃至上万条时,激烈倡议利用此筹划,可以带来宏大的性能晋升(激烈举荐)
复制代码 代码以下:
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参数化查询
like查询按照个人习惯将通配符写到参数值中或在SQL拼接都可,两种办法履行效果一样,在此不在详述
复制代码 代码以下:
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//将 % 写到参数值中
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种筹划做了个简单总结

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

此文章属怠惰的肥兔原创
　　以上是“SqlServer参数化查询之where in和like实现详解[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
  • sqlserver索引的原理及索引成立的注意事项小结
  • sqlserver数据库主键的生成方法小结(sqlserver,mysql)
  • sqlserver主键计划的注意点
  • CREATE FUNCTION sqlserver用户定义函数
  • 利用cmd号令行窗口操作SqlServer的办法
  • sqlserver bcp(数据导入导出工具)普通用法与号令详解
  • 重命名SQLServer数据库的办法
  • 利用SqlBulkCopy时应注意Sqlserver表中利用缺省值的列
  • sqlserver中将varchar范例转换为int型再举行排序的办法
  • sqlserver 改正列名及表名的sql语句
  • SQLServer顶用T—SQL号令查询一个数据库中有哪些表的sql语句
  • sqlserver 脚本和批处理指令小结
    •
    本文地址： 与您的QQ/BBS好友分享!
    [] [返回上一页] [打 印]
    • 好的评价　如果您觉得此文章好，就请您
        0%(0)
    • 差的评价　如果您觉得此文章差，就请您
        0%(0)

    文章评论评论内容只代表网友观点，与本站立场无关！

       评论摘要(共 0 条，得分 0 分，平均 0 分) 查看完整评论

    栏目导航

    赞助商链接

    免责条款 - 广告合作 - 下载声明 - 欢迎投稿 - 友情连接 -
    Copyright © 2020-2022 www.xiamiku.com. All Rights Reserved .