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

Question

I want to create a stored procedure which receives an array of integers and some other inputs like: CREATEPROCEDUREtest(field1varchar(4),field2varchar(4),arrayvarchar(255)) In the stored procedure I want to use it like this: ...WHEREsome_fieldIN( array)... The problem is that this way I only get the rows corresponding to the first integer in the array. Is there any way to make it work (I also tried using FIND_

P粉590428357 · Answer

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

Use , as delimiters to split the string,
Insert each value into the temporary table

So your temp table will have 1,2,3

Finally just use your temporary table

P粉713866425 · Answer

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.