Mysql prepare statment in 的一个陷阱

使用prepare statment设置in表达式的参数，结果没有像预想的那样，既没有出错，也没有返回全部的值，而是只返回了逗号分隔的第一值的结果。不知道mysql为什么要这么设计，还不如直接报错了好。

mysql> prepare stmt1 from 'select id,name from user where id in (?)'; 
mysql> set @a = '4208064,7303964,7303967,7355999'; 
mysql> execute stmt1 using @a ; 
+---------+----------+ 
| id | name | 
+---------+----------+ 
| 4208064 | aaaaaaaa | 
+---------+----------+ 
1 row in set (0.00 sec) 
mysql> select id, name from user where id in (4208064,7303964,7303967,7355999); 
+---------+------------+ 
| id | name | 
+---------+------------+ 
| 4208064 | aaaaaaaa | 
| 7303964 | bbbbbbbb | 
| 7303967 | cccccc | 
| 7355999 | ddddddddd | 
+---------+------------+ 
4 rows in set (0.00 sec)

如果非要用, 只能这样： [sourcecode]

mysql> prepare stmt1 from 'select id,name from user where id in (?,?,?,?)'; 
mysql> set @a = '4208064'; 
mysql> set @b = '7303964'; 
mysql> set @c = '7303967'; 
mysql> set @d = '7355999'; 
mysql> execute stmt1 using @a, @b, @c, @d; 
+---------+------------+ 
| id | name | 
+---------+------------+ 
| 4208064 | aaaaaaaa | 
| 7303964 | bbbbbbbb | 
| 7303967 | cccccc | 
| 7355999 | ddddddddd | 
+---------+------------+ 
4 rows in set (0.00 sec) 

[/sourcecode] 所以，你要在程序中正确的生成"?,?,?,?"，问好的个数和参数的个数对应。

转载于:https://my.oschina.net/komodo/blog/919189