小小地郁闷了一把，当“参数化查询”遭遇到"in”语句

[jiashie]

最近为了优化数据库执行效率，把原有代码中的用得最频繁的数据库操作改成了参数化查询的方式，但是。有个带有in的sql语句，就让人郁闷了。数据库中的数据老是没更新，检查了半天，最后还是把DEBUG_MODE编译参数打开，查看出错信息，才定位到错误。
'sqlStr = "update tbCommand set isHandled = 1 where id IN (?);"
执行时出错，将varchar转换成bigint时出错。

只好改成
sqlStr = "update tbCommand set isHandled = 1 where id=?;"
然后在一个循环中逐个更新。
网上也没找到这个问题的较好的解决方法。
msdn上有一篇HOW TO: Implement Helper Class to use a SqlParameter with a list of values in a IN clause，竟是用了临时表。

不知道有没人遇到过类似问题，最后是怎么解决的？

[li163]

什么数据库？你的字段是什么数据类型？

[jiashie]

[Quote=引用 3 楼 king06 的回复:]

拼接字符串是可以的,参数化不行.
in()语句里面包含的内容在数据库里不是简单的字符型,其中的逗号含义用自己赋值进去的变量不能取代.
我一般用的就是传一个字符串进去,然后在数据库存储过程里分割.
或者用临时表类型:SQL code
declare @t as table(col varchar(10))
[/Quote]
用临时表 和在代码里直接用for循环分割，再一个一个地update，哪个效率高？
看起来差不多吧。

[li163]

建议改用存储过程

[luofenghen]

Sql中split方法实现http://9bugaosu1.blog.163.com/blog/static/6272222520107214106342/

[li163]

貌似用字符串拼接也一样可以做的很好

[king06]

临时表快,也不麻烦. 不过我两者都用了,哈.
先传个字符串进去,然后循环分割,存到临时表,然后用in语句; 不然还得一条一条的执行,效率不那么如意哦

[lxq19851204]

[Quote=引用 5 楼 jiashie 的回复:]
引用 3 楼 king06 的回复:

拼接字符串是可以的,参数化不行.
in()语句里面包含的内容在数据库里不是简单的字符型,其中的逗号含义用自己赋值进去的变量不能取代.
我一般用的就是传一个字符串进去,然后在数据库存储过程里分割.
或者用临时表类型:SQL code
declare @t as table(col varchar(10))

用临时表 和在代码里直接用for循环……
[/Quote]
用临时表的效果比FOR快。

[king06]

给个样子吧,一般我不说这个方法的

declare @code as varchar(10)
declare @t table(col varchar(10))

while(charindex('#',@codes)<>0)   --@codes 是传进来的参数
begin
    insert @t(col) values (substring(@codes,1,charindex('#',@codes)-1))   		
	set @codes = stuff(@codes,1,charindex('#',@codes),'')   
end
if len(@codes)>=8 and len(@code)<=10
	insert @t(col) values (@codes) 

update tableB set field2='Yes' where field1 in(select col from @t)

[king06]

上例是我用到的一个存储过程里面的
@code 一般长度为8-10 ,故条件视你自己的情况而定

[Tiger_Zhao]

除非这些 id 是用户随意选择的，否则应该换成选取这些 id 子查询的条件。

[jiashie]

[Quote=引用 18 楼 chinaboyzyq 的回复:]

引用 2 楼 jiashie 的回复:

strid="4,5,6"

这里4 5 6在SQL server的in中是整型数据(int或bigint)，你的id是字符型(varchr),当id里没有数字以外的字符时是可以的(varchar到bigint的转换在SQL server里是隐性自动转换的)，如果有则出错。

最好的方法是strid="'4','5','6'",带单引号则不会……
[/Quote]
也试过在每个id前后加'，行不通。

[king06]

我在3F已经说过了

[jiashie]

[Quote=引用 17 楼 vbman2003 的回复:]

所谓参数化查询，就是将你sql语句中的?号作为对象来处理的，它不是将对象解析成字符串来处理你的n子句，这也是参数化查询能够防范sql注入的原因...
如果你in子句要处理比如4,5,6三个值，就要将sql语句解析成三个参数：
sqlStr = "update tbCommand set isHandled = 1 where id IN (?,?,?);"

还有关于参数的赋值处理，你可……
[/Quote]
那篇blog看了。有些地方值得借鉴。
不过，事先并不知道 IN 后面的参数个数，所以。。

[咸清]

拆分成多条语句批执行，效率未必差。

[vbman2003]

不过，事先并不知道 IN 后面的参数个数，所以。。
----------------------------------------
这种情况经常用到的，实际上就是拼接sql语句...

[咸清]

[Quote=引用 5 楼 jiashie 的回复:]

引用 3 楼 king06 的回复:

拼接字符串是可以的,参数化不行.
in()语句里面包含的内容在数据库里不是简单的字符型,其中的逗号含义用自己赋值进去的变量不能取代.
我一般用的就是传一个字符串进去,然后在数据库存储过程里分割.
或者用临时表类型:SQL code
declare @t as table(col varchar(10))

用临时表 和在代码里直接用for循……
[/Quote]
MS的参数化查询已经帮我们优化了，如果连续多条Update语句的话，效率会大幅提高！详见SQLServer的事件查看器。至于跟临时表对比，我没有测试过，不下结论。

[神马都能聊]

[Quote=引用 13 楼 tiger_zhao 的回复:]
除非这些 id 是用户随意选择的，否则应该换成选取这些 id 子查询的条件。
[/Quote]
有道理！

[贝隆]

一般情况下ID都是有规律的，比如在一定的范围以内，可以考虑使用Between。

[vbman2003]

所谓参数化查询，就是将你sql语句中的?号作为对象来处理的，它不是将对象解析成字符串来处理你的n子句，这也是参数化查询能够防范sql注入的原因...
如果你in子句要处理比如4,5,6三个值，就要将sql语句解析成三个参数：
sqlStr = "update tbCommand set isHandled = 1 where id IN (?,?,?);"

还有关于参数的赋值处理，你可以参考，希望对你有帮助：http://blog.csdn.net/vbman2003/archive/2010/12/06/6057503.aspx

[chinaboyzyq]

[Quote=引用 2 楼 jiashie 的回复:]

strid="4,5,6"
[/Quote]
这里4 5 6在SQL server的in中是整型数据(int或bigint)，你的id是字符型(varchr),当id里没有数字以外的字符时是可以的(varchar到bigint的转换在SQL server里是隐性自动转换的)，如果有则出错。

最好的方法是strid="'4','5','6'",带单引号则不会出错。