mysql - 在 MySQL 中，如何将整数列表传递给存储过程中的预准备语句？

Question

MySQL 5.0

CREATE TABLE `locationcodes` (
  `id` int,
  `customer` varchar(100),
  `locationcode` varchar(50),
  `parentid` int
);

insert into locationcodes values (1, 'Test, Inc.', 'California', NULL);
insert into locationcodes values (2, 'Test, Inc.', 'Los Angeles', 1);
insert into locationcodes values (3, 'Test, Inc.', 'San Francisco', 1);
insert into locationcodes values (4, 'Test, Inc.', 'Sacramento', 1);

存储过程:

CREATE PROCEDURE test (_location VARCHAR(100))
BEGIN

SET @location = _location;

SET @select='
  SELECT id, locationcode
  FROM locationcodes  
  WHERE customer="Test, Inc."
  AND id in (?)
  ';

PREPARE stmt FROM @select;
EXECUTE stmt USING @location;
DEALLOCATE PREPARE stmt;

END

调用流程:

call test('2, 3')

结果:

2, Los Angeles

结果只有一行，但我想要两行。挑战是准备好的语句看到参数是 varchar，并相应地在值周围加上引号。我不希望使用引号，这样我就可以构建 IN 语句，该怎么做？理想情况下，MySQL 应该有一个 ints 数据类型列表，这样我就不必使用 varchar，但这并不存在。

我需要保留准备好的语句以防止sql注入(inject)攻击。

Answer 1

似乎不可能将准备好的语句与 IN() 一起使用并绑定(bind)一个值。

出于某种原因，当相应比较中只有一个值需要绑定(bind)时，MySQL 的 API 会用 =(等于)替换 IN()。

我用我们的程序运行了一些测试，这就是我得到的。

使用您的原始过程，这就是 MySQL 生成的内容(test 是我的默认数据库):

((`test`.`locationcodes`.`customer` = 'Test, Inc.') and 
(`test`.`locationcodes`.`id` = '2,3'))

在这种情况下，字段 id 是整数，MySQL 在比较中将 '2,3' 转换为 2，这就是它返回洛杉矶的原因，'3,2' 显然会返回 '旧金山'。

为了确保这与字段类型(id 是整数)无关，我尝试将值与位置代码(varchar)进行比较，结果是相同的。

我更改了您的过程以绑定(bind)两个值而不是一个。

...
SET @select='
  SELECT id, locationcode
  FROM locationcodes  
  WHERE customer="Test, Inc."
  AND id in (?,?)
  ';

PREPARE stmt FROM @select;
EXECUTE stmt USING @location,@location2;
...

在这种情况下，可以正确创建语句。

((`test`.`locationcodes`.`customer` = 'Test, Inc.') and 
(`test`.`locationcodes`.`id` in ('2','3')))

话虽如此，我可以告诉您必须使用另一种方法通过准备好的语句来完成此任务。

经过测试，我发现了此错误报告:http://bugs.mysql.com/bug.php?id=16564

根据 Valeriy 的说法，使用这些值创建一个临时表并使用 JOIN。它应该有效。