How to split comma separated text in MySQL stored procedure

Question

How to split comma separated text (list of IDs) in MySQL stored procedure to use result in SQL "IN" statement.

SELECT * FROM table WHERE table.id IN (splitStringFunction(commaSeparatedData, ','));

Answer 1

This is simple as hell for MySQL:

SELECT * FROM table WHERE FIND_IN_SET(table.id, commaSeparatedData);

Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Answer 2

You could use a prepared statement inside the stored procedure to achieve this. You can create the whole select query as a string inside a variable and then concatenate in the comma delimited string into its IN clause. Then you can make a prepared statement from the query string variable and execute it.

DELIMITER ;;
create procedure testProc(in listString varchar(255))

BEGIN

set @query = concat('select * from testTable where id in (',listString,');');
prepare sql_query from @query;
execute sql_query;

END
;;

DELIMITER ;

call testProc("1,2,3");