74

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!

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Gruber
  • 4,478
  • 6
  • 47
  • 74

9 Answers9

69

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);
Devart
  • 119,203
  • 23
  • 166
  • 186
42

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
Sagiv Ofek
  • 25,190
  • 8
  • 60
  • 55
  • 2
    only downside is that it is slower than stored procedure option – kellogs Apr 19 '13 at 11:38
  • 1
    @kellogs - what do you mean? you can put it in stored procedure – Sagiv Ofek Apr 19 '13 at 15:54
  • 1
    I was unclear, sorry. This isn't about SP vs non-SP, but about WHERE IN vs FIND_IN_SET. The first method is a winner, no matter if in SP or not. – kellogs Apr 23 '13 at 00:53
  • 3
    Works with integers as well: FIND_IN_SET(product.Id, '1,2,3') – Pavel Shkleinik Jan 29 '16 at 19:23
  • Works! There is a detailed explanation on how you can use indexes to optimise the use of FIND_IN_SET in [this StackOverflow thread](https://stackoverflow.com/a/38002986/6388351). I found it insightful. – Benny64 Mar 04 '22 at 01:59
26

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');
Raj
  • 706
  • 8
  • 18
  • this will work, but it won't be able to optimise the query, as it will have to check each record to see if it matches, i.e. if there are 1,000,000 rows in the table, it will have to check to see if the id is 'in the set' for each row. – Rich S Feb 27 '20 at 11:55
  • @RichS Yes. But I didn't found any other easier way to search in array. – Raj Mar 03 '20 at 05:54
  • You will face a full table scan problem because the index will not work on find_in_set, a solution is to add more condition to the where statement to reduce the scanned rows. – Firas Abd Alrahman Oct 05 '20 at 23:03
13

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
bobobobo
  • 64,917
  • 62
  • 258
  • 363
  • The idea of using a TEMPORARY TABLE, even ENGINE=MEMORY, is good, but you do a detour from the original argument type. You need to pass a CSV value as argument, then parse it to seed that tmp table. – Fabien Haddadi Oct 15 '20 at 17:44
5

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.

Gruber
  • 4,478
  • 6
  • 47
  • 74
3

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)
KCD
  • 9,873
  • 5
  • 66
  • 75
1

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;
pvilas
  • 812
  • 6
  • 7
  • 1
    Didn't know about the `ELT()` function. But how do you declare the "array" of strings variable, in this case `t_tipos`? How to specify the three strings Banana, Apple, Orange? – Gruber Mar 13 '13 at 13:10
1

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');
Alan Stewart
  • 93
  • 2
  • 7
0

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)
ahmkara
  • 559
  • 6
  • 6