Rewrite the title as: Passing array of integers to MySQL stored procedure

Question

I want to create a stored procedure that receives an array of integers and some other inputs, for example:

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

In the stored procedure I want to use it like this:

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

The problem is that this way I only get the row corresponding to the first integer in the array.

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

The call I made to test the stored procedure was CALL test (12, 13, '1, 2, 3').

Answer 1

There is no concept of array. So this is probably what you can do

The value of your array variable is '1, 2, 3'as a string

1. Use , as delimiters to split the string,

2. Insert each value into the temporary table

So your temp table will have 1,2,3

Finally just use your temporary table

Answer 2

FIND_IN_SET() works, but there can't be spaces in the number string.

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 |
+-------------------------+

Therefore, you should form the list without spaces before passing it to the procedure, or use REPLACE() to remove the spaces in the procedure.