Pass array to MySQL stored routine

Question

I need to pass an array of strings as parameter to a MySQL stored routine. The array could be long and its number of elements is not fixed. I then want to put the string values into an in-memory table with one column, so I can work with the data. I don't know if this can be done in MySQL. Maybe dirty workarounds are needed.

For example, I have the string values:

Banana, Apple, Orange 

Now I want to get data on these fruits from my MySQL Fruits table. Pseudo code:

create function GetFruits(Array fruitArray)     declare @temp table as       fruitName varchar(100)    end     @temp = convert fruitArray to table    select * from Fruits where Name in (select fruitName from @temp) end 

Microsoft SQL Server allows you to use the TEXT datatype and submit the array as an XML string, swiftly creating the in-memory table. However, I don't think that technique is possible in MySQL.

Any help on how to do this would be appreciated!

Answer 1

You can pass a string with your list and use a prepared statements to run a query, e.g. -

DELIMITER $$  CREATE PROCEDURE GetFruits(IN fruitArray VARCHAR(255)) BEGIN    SET @sql = CONCAT('SELECT * FROM Fruits WHERE Name IN (', fruitArray, ')');   PREPARE stmt FROM @sql;   EXECUTE stmt;   DEALLOCATE PREPARE stmt;  END $$  DELIMITER ; 

How to use:

SET @fruitArray = '\'apple\',\'banana\''; CALL GetFruits(@fruitArray); 

Answer 2

Simply use FIND_IN_SET like that:

mysql> SELECT FIND_IN_SET('b','a,b,c,d');         -> 2 

so you can do:

select * from Fruits where FIND_IN_SET(fruit, fruitArray) > 0