Geek Answers Handbook

Using a custom MySQL variable in a stored procedure for an in clause

When sending a string of comma-separated identifiers like varchar to a MySQL stored procedure, I cannot use this string as part of an IN clause to return the correct results. The string is truncated as decimal and only the first value is used.

I thought I could get around this by preparing and then following the instructions, but that still returned a match for the first value.

Code examples can make things more clear. I want to convert the following to a stored procedure (with dynamic in a section):

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 a stored procedure I get only a match for the city '1':

 call cities_select_by_ids_prepare('1, 2, 3');

Here, create and paste the 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');
+4
source share
3 answers

due to how parameterization works, this is not possible.

nearest you can get this:

 where find_in_set(id, ?)

but it will not scale as well as using the index.

+2
source

Try it.

 DROP PROCEDURE IF EXISTS `cities_select_by_ids_2`; CREATE PROCEDURE `cities_select_by_ids_2`( in cityIDs varchar(1000) ) BEGIN #- ix - index into the list of city IDs # cid - city ID SET @ix := 1; SET @cid := substring_index(cityIDs, ',', @ix); LOOP_1: WHILE (@cid is not null) DO SELECT id, name FROM cities WHERE id in (@cid) ; #-- substring_index returns complete cityIDs string when index is > number of elements IF (length(substring_index(cityIDs, ',', @ix)) >= length(cityIDs)) THEN LEAVE LOOP_1; END IF; SET @ix := @ix + 1; SET @cid = substring_index(substring_index(cityIDs, ',', @ix), ',', -1); END WHILE; END #---- call cities_select_by_ids_2('1, 2');
+4
source

Another alternative.

The query below only works for supported approved instructions inside stored procedures. MySQL 5.0 I think. This is much better than using find_in_set. Use double quotation marks "" for the string.

 /* Check your mysql version */ BEGIN SET @sql_text:=concat( 'SELECT id, name FROM db.table WHERE id IN (', /* BEGIN Parameter */ '91,57', /* END Parameter */ ') ORDER BY id ASC'); PREPARE stmt from @sql_text; EXECUTE stmt ; DEALLOCATE PREPARE stmt; END
+3
source

More articles:


All Articles