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

If your query set is a limited range integer (e.g. max 100) and you need better performance on complex queries, I've got an extended solution from the above answers:

declare item int;

DROP TABLE IF EXISTS MyTemporaryTable;
CREATE TEMPORARY TABLE MyTemporaryTable ( columnName int );

set item = 1;
while (item < 100) do
   insert into MyTemporaryTable select item where FIND_IN_SET(item, app_list);
   set item = item + 1;
end while;

SELECT * FROM table where id in (select * from MyTemporaryTable)

Answer 2

I'm not sure if this is fully answering the question (it isn't), but it's the solution I came up with for my similar problem. Here I try to just use LOCATE() just once per delimiter.

-- *****************************************************************************
-- test_PVreplace

DROP FUNCTION IF EXISTS test_PVreplace;

delimiter //
CREATE FUNCTION test_PVreplace (
   str TEXT,   -- String to do search'n'replace on
   pv TEXT     -- Parameter/value pairs 'p1=v1|p2=v2|p3=v3'
   )
   RETURNS TEXT

-- Replace specific tags with specific values.

sproc:BEGIN
   DECLARE idx INT;
   DECLARE idx0 INT DEFAULT 1;   -- 1-origined, not 0-origined
   DECLARE len INT;
   DECLARE sPV TEXT;
   DECLARE iPV INT;
   DECLARE sP TEXT;
   DECLARE sV TEXT;

   -- P/V string *must* end with a delimiter.

   IF (RIGHT (pv, 1) <> '|') THEN
      SET pv = CONCAT (pv, '|');
      END IF;

   -- Find all the P/V pairs.

   SELECT LOCATE ('|', pv, idx0) INTO idx;
   WHILE (idx > 0) DO
      SET len = idx - idx0;
      SELECT SUBSTRING(pv, idx0, len) INTO sPV;

      -- Found a P/V pair.  Break it up.

      SELECT LOCATE ('=', sPV) INTO iPV;
      IF (iPV = 0) THEN
         SET sP = sPV;
         SET sV = '';
      ELSE
         SELECT SUBSTRING(sPV, 1, iPV-1) INTO sP;
         SELECT SUBSTRING(sPV, iPV+1) INTO sV;
         END IF;

      -- Do the substitution(s).

      SELECT REPLACE (str, sP, sV) INTO str;

      -- Do next P/V pair.

      SET idx0 = idx + 1;
      SELECT LOCATE ('|', pv, idx0) INTO idx;
      END WHILE;
   RETURN (str);
END//
delimiter ;

SELECT test_PVreplace ('%one% %two% %three%', '%one%=1|%two%=2|%three%=3');
SELECT test_PVreplace ('%one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', NULL);
SELECT test_PVreplace ('%one% %two% %three%', '%one%=%two%|%two%=%three%|%three%=III');

Answer 3

This helps for me to do IN condition Hope this will help you..

CREATE  PROCEDURE `test`(IN Array_String VARCHAR(100))
BEGIN
    SELECT * FROM Table_Name
    WHERE FIND_IN_SET(field_name_to_search, Array_String);

END//;

Calling:

 call test('3,2,1');

Answer 4

If you don't want to use temporary tables here is a split string like function you can use

SET @Array = 'one,two,three,four';
SET @ArrayIndex = 2;
SELECT CASE 
    WHEN @Array REGEXP CONCAT('((,).*){',@ArrayIndex,'}') 
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@Array,',',@ArrayIndex+1),',',-1) 
    ELSE NULL
END AS Result;

• SUBSTRING_INDEX(string, delim, n) returns the first n
• SUBSTRING_INDEX(string, delim, -1) returns the last only
• REGEXP '((delim).*){n}' checks if there are n delimiters (i.e. you are in bounds)

Answer 5

Use a join with a temporary table. You don't need to pass temporary tables to functions, they are global.

create temporary table ids( id int ) ;
insert into ids values (1),(2),(3) ;

delimiter //
drop procedure if exists tsel //
create procedure tsel() -- uses temporary table named ids. no params
READS SQL DATA
BEGIN
  -- use the temporary table `ids` in the SELECT statement or
  -- whatever query you have
  select * from Users INNER JOIN ids on userId=ids.id ;
END //
DELIMITER ;

CALL tsel() ; -- call the procedure

Answer 6

This simulates a character array but you can substitute SUBSTR for ELT to simulate a string array

declare t_tipos varchar(255) default 'ABCDE';
declare t_actual char(1);
declare t_indice integer default 1;
while t_indice<length(t_tipos)+1 do
    set t_actual=SUBSTR(t_tipos,t_indice,1);
        select t_actual;
        set t_indice=t_indice+1;
end while;

Answer 7

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

Answer 8

I've come up with an awkward but functional solution for my problem. It works for a one-dimensional array (more dimensions would be tricky) and input that fits into a varchar:

  declare pos int;           -- Keeping track of the next item's position
  declare item varchar(100); -- A single item of the input
  declare breaker int;       -- Safeguard for while loop 

  -- The string must end with the delimiter
  if right(inputString, 1) <> '|' then
     set inputString = concat(inputString, '|');
  end if;

  DROP TABLE IF EXISTS MyTemporaryTable;
  CREATE TEMPORARY TABLE MyTemporaryTable ( columnName varchar(100) );
  set breaker = 0;

  while (breaker < 2000) && (length(inputString) > 1) do
     -- Iterate looking for the delimiter, add rows to temporary table.
     set breaker = breaker + 1;
     set pos = INSTR(inputString, '|');
     set item = LEFT(inputString, pos - 1);
     set inputString = substring(inputString, pos + 1);
     insert into MyTemporaryTable values(item);
  end while;

For example, input for this code could be the string Apple|Banana|Orange. MyTemporaryTable will be populated with three rows containing the strings Apple, Banana, and Orange respectively.

I thought the slow speed of string handling would render this approach useless, but it was quick enough (only a fraction of a second for a 1,000 entries array).

Hope this helps somebody.

Answer 9

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);