Sending a list of values of the same field to MySQL stored procedure

I have a MySQL table with ID as a primary key and other for this matter non-important fields. What I would like to do is delete multiple records by sending a list of IDs for deletion as a parameter to stored procedure.

I know how to do this manually (building a query directly in PHP) but I would like to avoid that and do all my SQL directly in the DB.

Tried searching SO but couldn't find any related questions. So开发者_StackOverflow中文版rry if this is a duplicate.

Thanks

In accordance to http://dev.mysql.com/doc/refman/5.1/en/faqs-stored-procs.html#qandaitem-B-4-1-17 you can't do it directly.

But I think you can try to the following trick:

1. Create string of you ids in php like 'id1,id2,id3'.
2. Use prepared statement for binding this sting on fly.

Maybe it helps.

You could try something like

DELETE FROM sometable WHERE FIND_IN_SET(idfield, @param)

no idea if this'd work (and don't have access to a mysql instance right now to test on). Basically the problem is that if you pass in a comma-separated value list into a paramter, it'll just be a string inside the sproc, and doing a WHERE id IN ('1,2,3') would fail, since that's just a simple string and not at all the same as WHERE id IN (1,2,3). The find_in_set() function should take care of that.

I gave +1 to @Marc B for clever use of FIND_IN_SET(). It won't be able to use an index, so the performance won't be good, but it should work.

Another solution that can work (but will be slow as well, because it can't use an index):

DELETE FROM sometable 
WHERE CONCAT(',', param, ',') LIKE CONCAT('%,', idfield, ',%')

The solution that @Andrej L describes isn't really parameter binding, it's interpolation of a stored procedure argument into a dynamic SQL string prior to preparing it.

SET sql = CONCAT('DELETE FROM sometable WHERE idfield IN (', param, ')');
PREPARE stmt FROM sql;
EXECUTE stmt;

You can't parameterize a list of values with a single parameter, even if the parameter's value looks like a comma-separated list of integers.

Interpolation can work, and it will benefit from an index, but be careful to filter the string so it contains only numeric digits and commas. Otherwise you introduce a significant risk of SQL injection (debunking the claim that some people make that stored procedures are inherently more secure).