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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt       THEN       SET pv   CONCAT  pv              END IF      -- Find all the P V pairs      SELECT LOCATE       pv  idx0  INTO idx     WHILE  idx  gt  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

User · Answer

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

User · Answer

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   lt  gt      then      set inputString   concat inputString          end if     DROP TABLE IF EXISTS MyTemporaryTable    CREATE TEMPORARY TABLE MyTemporaryTable   columnName varchar 100       set breaker   0     while  breaker  lt  2000   amp  amp   length inputString   gt  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

User · Answer

Simply use FIND IN SET like that   mysql gt  SELECT FIND IN SET  b   a b c d            - gt  2   so you can do   select   from Fruits where FIND IN SET fruit  fruitArray   gt  0

User · Answer

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

[mysql] Pass array to MySQL stored routine

Examples related to mysql

Examples related to arrays

Examples related to stored-procedures

Examples related to parameter-passing