mysql 存储过程 for in_在存储过程中对“ in”子句使用MySQL用户定义的变量

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.

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 ;

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.

the closest you can get it this:

where find_in_set(id, ?)

but this will not scale as as can not use an index.