Pass Array to MySQL Routine IN operator

Question

I want to exclude a variable number of users/id's from my results. Is there a way to pass a variable number of usernames/id's to the IN operator? Simplified code as follows: -

CREATE DEFINER=`root`@`localhost` PROCEDURE `myroutine`(@userids)
--possible process here--
BEGIN
SELECT * FROM mytable WHERE `myid` NOT IN (@useridsprocessed)
END

yes, with php (you tagged it) you can populate the @useridsprocessed var — Marco Mura, Dec 22 '14 at 15:52
yes. even just a simple `myroutine('1,2,3')` where you pass the IDs as a single string would do, but then you need to prepare/execute your select dynamically, since `not in (@userids)` wouldn't work. it'd be the equivalent of `not in ('1,2,3')`. — Marc B, Dec 22 '14 at 15:52
Are you looking for [`FIND_IN_SET`](https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set)? Warning: You asked for IN operator, this is a string function whichs result you could use within IN if you cast to bool. — hakre, Dec 22 '14 at 16:00
Maybe this will help: http://explainextended.com/2010/11/03/10-things-in-mysql-that-wont-work-as-expected/ (See `#2`) — gen_Eric, Dec 22 '14 at 16:01
Thanks, this 'prepare' and 'execute' is something I haven't yet covered, so I will have to look into it and get back to you. — user2790911, Dec 22 '14 at 16:08
Marco Mura - I can build the whole lot with php, but that wouldn't be taking advantage of a MySQL routine. Are you saying that I can build something with php that I can inject into my routine? — user2790911, Dec 22 '14 at 16:34
Okay, thanks. I got the extra detail I needed from Devart here : - http://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine . I am assuming this is the preferred way of doing it — user2790911, Dec 22 '14 at 17:21

score 0 · Answer 1 · edited May 26 '16 at 14:08

For the sake of closure, this is the best answer I could get to work, but it strikes me as messy.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `mytable`(IN excluded VARCHAR(255))
BEGIN
SET @sql = CONCAT('SELECT * FROM entries WHERE `id` NOT IN (', excluded,')');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

called as follows : -

SET @excluded = '\'19\',\'26\',\'27\'';
call liquidfinger.mytable(@excluded);

Pass Array to MySQL Routine IN operator

1 Answers1