How to pass a list of IDs to MySQL stored procedure?

Question

I'm writing a stored procedure which should pass its arguments to IN (..) part of query in the procedure body, like this:

DELIMITER //  CREATE PROCEDURE `get_users_per_app` (id_list TEXT) BEGIN     SELECT app_id, GROUP_CONCAT(user_id) FROM app_users WHERE app_id IN (id_list) GROUP BY app_id; END//  DELIMITER ; 

This, obviously, doesn't work because when I pass a textual value, id_list is interpolated as an integer and only first ID is considered and used inside of IN() condition.

I realize that this particular type of procedure could be instead replaced by the contained query, but I think that my question still stands - what if I needed to pass this kind of data?

I also realize that this approach of query might not be considered the best practice, but in my use case it's actually better than returning a flat list of ID-ID pairs..

Answer 1

You should be able to use MySQL's FIND_IN_SET() to use the list of ids:

CREATE PROCEDURE `get_users_per_app` (id_list TEXT) BEGIN     SELECT         app_id, GROUP_CONCAT(user_id)     FROM         app_users     WHERE         FIND_IN_SET(app_id, id_list) > 0     GROUP BY app_id;     ...