vlio20 vlio20 - 8 days ago 5
MySQL Question

Passing an integer array to mysql procedure

I want to create a stored procedure which receives array of integers and some other input, for example:

CREATE PROCEDURE test (field1 varchar(4), field2 varchar(4), array varchar (255))


and in the stored procedure I want to use it like this:

...
WHERE some_field IN (array)
...


The problem is that in this way I am getting only the rows which correspondence to the first integer in the array.

Is there any way to make it work (I also tried to use
FIND_IN_SET
but it did exactly the same as
IN
)?

The call that I am making for testing the stored procedure is
CALL test (12, 13, '1, 2, 3')
.

Answer

FIND_IN_SET() works, but you can't have spaces in the string of numbers.

Demo:

mysql> select find_in_set(2, '1, 2, 3');
+---------------------------+
| find_in_set(2, '1, 2, 3') |
+---------------------------+
|                         0 |
+---------------------------+

mysql> select find_in_set(2, '1,2,3');
+-------------------------+
| find_in_set(2, '1,2,3') |
+-------------------------+
|                       2 |
+-------------------------+

So you should either form the list with no spaces before you pass it to your procedure, or else in the procedure, use REPLACE() to strip out spaces.