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

Question

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. Sorry if this is a duplicate.

Thanks

score 3 · Answer 1 · answered Aug 25 '11 at 17:38

3

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:

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

Maybe it helps.

answered Aug 25 '11 at 17:38

Andrej

7,474
1
19
21

No problem. Bill expanded my idea. – Andrej Aug 25 '11 at 18:17

score 2 · Answer 2 · answered Aug 25 '11 at 17:39

2

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.

answered Aug 25 '11 at 17:39

Marc B

356,200
43
426
500

Thanks a lot, this worked. I'm not proficient in MySQL so didn't know what to search for exactly. I remember the hassle I had with Oracle and casting comma delimited strings to specific types and then using them in a query to achieve this result. :) – phil Aug 25 '11 at 17:48

score 1 · Accepted Answer · edited May 23 '17 at 12:19

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).

Thanks a lot, accepted this response because of additional information. — phil, Aug 25 '11 at 18:06

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

3 Answers3