Sql Server参数化查询之where in和like实现详解

最新推荐文章于 2024-03-04 09:21:39 发布
最新推荐文章于 2024-03-04 09:21:39 发布
阅读量464 收藏
点赞数
分类专栏： SQLSERVER 数据库优化

http://www.cnblogs.com/lzrabbit/archive/2012/04/22/2465313.html

身为一名小小的程序猿，在日常开发中不可以避免的要和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();
}
复制代码

方案5 使用xml参数

对sql server xml类型参数不熟悉的童鞋需要先了解下XQuery概念，这里简单提下XQuery 是用来从 XML 文档查找和提取元素及属性的语言，简单说就是用于查询xml的语言说到这就会牵着到XPath，其实XPath是XQuery的一个子集，XQuery 1.0 和 XPath 2.0 共享相同的数据模型，并支持相同的函数和运算符，XPath的方法均适用于XQuery，假如您已经学习了 XPath，那么学习 XQuery 也不会有问题。详见http://www.w3school.com.cn/xquery/xquery_intro.asp

XQuery概念了解后需要进一步了解下Sql Server对xml的支持函数，主要为query()、nodes()、exist()、value()、modify() ，详见http://msdn.microsoft.com/zh-cn/library/ms190798.aspx

使用xml方式实现where in时有两种实现方式，使用value和exist，在这里推荐使用exist方法，msdn是这样描述的：

D.使用 exist() 方法而不使用 value() 方法

由于性能原因，不在谓词中使用 value() 方法与关系值进行比较，而改用具有 sql:column() 的 exist()。

http://msdn.microsoft.com/zh-cn/library/ms178030.aspx

使用xml的value方法实现(不推荐)

复制代码 
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <UserID>1</UserID>
            <UserID>2</UserID>
            <UserID>5</UserID>
        </root>";
    SqlCommand comm = conn.CreateCommand();
    //不推荐使用value方法实现，性能相对exist要低
    comm.CommandText = @"select * from Users
            where exists
            (
                select 1 from @xml.nodes('/root/UserID') as T(c)
                where T.c.value('text()[1]','int')= Users.UserID
            )";

    //也可以这样写，结果是一样的
    //comm.CommandText = @"select * from Users
    //                    where UserID in
    //                    (
    //                        select T.c.value('text()[1]','int') from @xml.nodes('/root/UserID') as T(c)
    //                    )
    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}
复制代码

使用xml的exist方法实现(推荐)

复制代码 
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <UserID>1</UserID>
            <UserID>2</UserID>
            <UserID>5</UserID>
        </root>";
    SqlCommand comm = conn.CreateCommand();
    
    //使用xml的exist方法实现这样能够获得较高的性能
    comm.CommandText = @"select * from Users where @xml.exist('/root/UserID[text()=sql:column(""UserID"")]')=1";    
    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}
复制代码

列举下不同xml结构的查询方法示例，在实际使用中经常因为不同的xml结构经常伤透了脑筋

复制代码 
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <User>
                <UserID>1</UserID>
            </User>
            <User>
                <UserID>2</UserID>
            </User>    
            <User>
                <UserID>5</UserID>
            </User>    
        </root>";
    SqlCommand comm = conn.CreateCommand();
   
    //不推荐使用value方法实现，性能相对exist要低
    comm.CommandText = @"select * from Users
            where UserID in
            (
                select T.c.value('UserID[1]','int') from @xml.nodes('/root/User') as T(c)
            )";                   
    //也可以这样写，结果是一样的
    //comm.CommandText = @"select * from Users
    //                    where exists
    //                    (
    //                        select 1 from @xml.nodes('/root/User') as T(c)
    //                        where T.c.value('UserID[1]','int') = Users.UserID
    //                    )";
    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}
复制代码
复制代码 
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <User>
                <UserID>1</UserID>
            </User>
            <User>
                <UserID>2</UserID>
            </User>    
            <User>
                <UserID>5</UserID>
            </User>    
        </root>";
    SqlCommand comm = conn.CreateCommand();
    //使用xml的exist方法实现这样能够获得较高的性能
    comm.CommandText = @"select * from Users where @xml.exist('/root/User[UserID=sql:column(""UserID"")]')=1";

    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}
复制代码

 

使用xml参数时需要注意点:

　　1.不同于SQL语句默认不区分大小写，xml的XQuery表达式是严格区分大小写的，所以书写时一定注意大小写问题

　　2.使用exist时sql:column() 中的列名须使用双引号，如sql:column("UserID")，若非要使用单引号需要连续输入两个单引号 sql:column(''UserID'')

　　3.不管是where in或是其他情况下使用xml查询时能用exist(看清楚了不是sql里的exists)方法就用exist方法，我们不去刻意追求性能的优化，但能顺手为之的话何乐而不为呢。

方案6 使用表值参数(Table-Valued Parameters 简称TVP Sql Server2008开始支持)

按照msdn描述TVP参数在数据量小于1000时有着很出色的性能，关于TVP可以参考 http://msdn.microsoft.com/en-us/library/bb510489.aspx

这里主要介绍如何使用TVP实现DataTable集合传参实现where in

1.使用表值参数，首先在数据库创建表值函数

create type IntCollectionTVP as Table(ID int)

2.表值函数创建好后进行c#调用，

注意点：

　　1.需要SqlParameter中的SqlDbType设置为SqlDbType.Structured然后需要设置TypeName为在数据库中创建的表值函数名，本示例中为IntCollectionTVP

　　2.构造的DataTabel列数必须和表值函数定义的一样，具体列名随意，无需和表值函数定义的列名一致，数据类型可以随意，但还是建议和表值类型定义的保持一致，一来省去隐式类型转换，二来可以在初始化DataTabel时就将不合法的参数过滤掉

　　3.建议定义tvp的时候最好查询条件里的类型和tvp对应字段类型保持一致，这样可以避免隐式类型转换带来的性能损失

复制代码 
DataTable resultDt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand comm = conn.CreateCommand();
    comm.CommandText = @"select  * from Users(nolock)
            where exists
            (
                select 1 from @MyTvp tvp  
                where tvp.ID=Users.UserID
            )";

    //构造需要传参的TVP DataTable
    DataTable tvpDt = new DataTable();
    //为表添加列，列数需要和表值函数IntCollectionTVP保值一致，列名可以不一样
    tvpDt.Columns.Add("myid", typeof(int));
    //添加数据
    tvpDt.Rows.Add(1);
    tvpDt.Rows.Add(2);
    tvpDt.Rows.Add(3);
    tvpDt.Rows.Add(4);     
    //这里的TypeName对应我们定义的表值函数名
    comm.Parameters.Add(new SqlParameter("@MyTvp", SqlDbType.Structured) { Value = tvpDt, TypeName = "IntCollectionTVP" });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(resultDt);
    }
}
复制代码

 

 总结：

至此，一共总结了6六种where参数化实现，分别如下

1.使用CHARINDEX或like实现where in 参数化

2.使用exec动态执行SQl实现where in 参数化

3.为每一个参数生成一个参数实现where in 参数化

4.使用临时表实现where in 参数化

5.使用xml参数实现where in 参数化

6.使用表值参数(TVP)实现where in 参数化

其中前4种在Sql Server参数化查询之where in和like实现详解 一文中进行了列举和示例

6种方法，6种思路，

其中方法1 等于完全弃用了索引，若无特殊需要不建议采用，

方法2 本质上合拼SQL没啥区别与其用方法2自欺其人还不如直接拼接SQL来的实惠

方法3 受参数个数(做多2100个参数)限制，而且若传的参数过多性能如何有待验证，可以酌情使用

方法4 示例中采用的临时表，其实可以换成表变量性能也许会更好些，不过写法上有些繁琐，可以具体的封装成一个函数会好些(推荐)

方法5 使用xml传参，既然有这种类型说明性能上应该还不错，其它会比拼接SQL好很多，使用上也还比较方便，不过需要开发人员对xml查询有一定了解才行(推荐)

方法6 tvp方式sql server2008以后才可以使用，很好很强大，若只为where in 的话可以定义几个tvp where in问题就很容易解决了，而且是强类型也更容易理解(推荐)

不好去评论具体那种方法最好，还是那句老话合适的最好。


mituan1234567
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SqlServer参数化查询之where in和like实现详解
12-15
然而如何实现where in和like的参数化查询，是个让不少人头疼的问题。 where in 的参数化查询实现 首先说一下我们常用的办法，直接拼SQL实现，一般情况下都能满足需要 代码如下: string userIds = “1,2,3,4”; using...
Sql Server参数化查询之where in和like实现详解(转载综合)
05-16 183
Sql Server参数化查询之where in和like实现详解http://www.cnblogs.com/lzrabbit/archive/2012/04/22/2465313.html文章导读拼SQL实现where in查询使用CHARINDEX或like实现where in 参数化使用exec动态执行SQl实现where in 参数化为每一个参数生成一个参数实现where ...
sql 语句 where in的使用 where like "%我"
kxukai的博客
09-19 1479
sql语句 where in 在 一堆数据 WHERE S.sid in( SELECT sid //在子查询的结果中 找 匹配的 sid FROM SC GROUP BY SID HAVING COUNT(CID) <( SELECT COUNT(Course.CID) FROM Course ) ) ...
mysql like in_mysql——LIKE关键字与IN关键字组合查询
weixin_39837352的博客
01-18 1299
首先，创建一张 students 表 。DROP TABLE IF EXISTS `students`;CREATE TABLE `students` ( `id` int(11) NOT NULL COMMENT '主键id', `id_card` varchar(32) DEFAULT NULL COMMENT '学生证号', `name` varchar(32) DEFAULT NULL C...
如何把一个字符串转化为可用的sql in条件
欧拉拉
10-13 278
【代码】如何把一个字符串转化为可用的sql in条件。
SQL中in参数化的用法
05-06
SQL 中 IN 参数化的用法详解 在 SQL 中，对于 IN 操作符的使用是非常常见的...我们可以使用参数化查询来实现 where in 和 like 的参数化查询，提高 SQL 的查询性能和安全性。但是需要根据实际情况选择合适的解决方案。
精通SQL--结构化查询语言详解
12-11
10.2.2 in子查询实现集合交和集合差运算 191 10.2.3 exists子查询 192 10.2.4 exists子查询实现两表交集 194 10.2.5 some/all子查询 195 10.2.6 unique子查询 197 10.3 相关子查询 198 10.3.1 使用in引入相关...
sql server 数据库like 实现参数化的思路
最新发布
cjh16606260986的博客
03-04 645
CHAEINDEX示例：CHARINDEX('张三',all_path_name)，若 > 0表示存在，否则不存在。expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000。CHARINDEX原理：通过能够找到对应的字符串，则返回该字符串位置，否则返回0。start_location：开始查找的位置，为空时默认从第一位开始查找。1.使用CHARINDEX涵数返回非-1值表是有值，否则没有查询数据。expressionToSearch ：用于被查找的字符串。
sqlserver的存储过程与 where in 多值参数
02-23
自己总结的 关于sqlserver存储过程与多值参数的解决办法。
sql语句like多个条件的写法实例
09-10
介绍了sql语句like多个条件的写法实例，有需要的朋友可以参考一下
关于SqlParameter中IN子句查询的问题
weixin_30471065的博客
08-26 432
今天调试到方法中代码： String hotelCodes =”’000000’,’111111’,’222222’”; string sqltext ="select * from HotelMedalInfo where hotelCode in（@hotelCodes）"; SqlParameter[] parameters = { new SqlParameter("@hot...
MySql中 where IN 中多个参数查询
热门推荐
weixin_40725027的博客
10-11 3万+
mysql查询语句where条件in 正常情况需要查询的语句： select *from temp where id in ('1','2','3','4','5') 在实际过程中发现一直查不出数据，实际上能够查出数据，弄得一直很郁闷，找不出原因。 通过各终尝试，最后在项目中通过断点找出原因。 在进行查询时in中自动添加了单引号，如： select *from temp where id in ...
go语言实现where in查询
每天学习一点点的博客
08-21 4559
有时候需要一些条件查询操作，比如查询某字段出现在字符串数组集合中的所有记录，则需要进行where in条件查询，字符串数组在where in参数中的格式为(‘aaa’,‘bbb’,‘ccc’)，字符串必须用单引号’ '括起来 str := []string{"aaa","bbb","ccc","ddd"} sql := "select * from table_name where columes in ('%s')" params := strings.Join(str,"','")//params
where in 数据查询的用法
Fredrik的博客
04-05 808
---查询被当做黑名单人物次数排名前20的用户 select Top 20 db_owner.Relation.QQID,COUNT(db_owner.Relation.RelationStatus) AS 黑名单次数 from  db_owner.Relation group by db_owner.Relation.QQID,db_owner.Relation.RelationStatu
SQLServer数据库的查询和视图ppt课件.ppt
11-16
SQLServer数据库的查询和视图ppt课件.ppt；SQLServer数据库的查询和视图ppt课件.ppt;第4章 数据库的查询和视图4.1 数据库的查询4.2 视 图.4.1 数据库的查询下面介绍SELECT语句，它是T-SQL的核心。语法主体格式如下：...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值