在存储过程中对"in"子句使用MySQL用户定义的变量 [英] Using MySQL user-defined variable in stored procedure for 'in' clause
问题描述
当将以逗号分隔的ID字符串(作为varchar)发送到MySQL存储过程时,我不能将该字符串用作IN子句的一部分,并返回正确的结果.字符串被截断为十进制,并且仅使用第一个值.
When sending a string of comma separated ids, as a varchar, to a MySQL stored procedure I can not use the string as part of an IN clause with the correct results returned. The string is truncated as a decimal and only the first value is being used.
我认为我可以通过准备然后执行该语句来解决此问题,但这仍然只返回第一个值的匹配项.
I thought I would be able to get around this by preparing and then executing the statement, but this still only returned a match for the first value.
代码示例可能会让事情变得更清楚.我想将以下内容转换为存储过程(使用in子句动态):
Code examples may make things a bit clearer. I want to convert the following into a stored procedure (with the in clause dynamic):
select id, name from cities where id in (1,2,3);
这是使用准备好的语句的存储过程:
This is my stored procedure using a prepared statement:
DROP PROCEDURE IF EXISTS `cities_select_by_ids` $$
CREATE PROCEDURE `cities_select_by_ids`(
in _cityIds varchar(1000)
)
BEGIN
SET @cityIds = _cityIds;
PREPARE stmt FROM '
select
id,
name
from cities
where id in (?);
';
EXECUTE stmt USING @cityIds;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
调用存储过程时,只会得到城市"1"的匹配项:
Calling the stored procedure I only get a match for the city '1':
call cities_select_by_ids_prepare('1, 2, 3');
这是用于表和数据的创建和插入脚本:
Here is a create and insert script for the table and data:
CREATE TABLE cities (
id int(10) unsigned NOT NULL auto_increment,
name varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
insert into cities (name) values ('London'), ('Manchester'), ('Bristol'), ('Birmingham'), ('Brighton');
推荐答案
由于参数化的工作方式,因此不可能.
due to the way parameterization works, this is not possible.
最接近的它可以得到:
where find_in_set(id, ?)
但这不会像不能使用索引那样缩放.
but this will not scale as as can not use an index.
这篇关于在存储过程中对"in"子句使用MySQL用户定义的变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!