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!
This question is related to
mysql
arrays
stored-procedures
parameter-passing
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 nSUBSTRING_INDEX(string, delim, -1) returns the last onlyREGEXP '((delim).*){n}' checks if there are n delimiters (i.e. you are in bounds)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');
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;
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
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.
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
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');
Source: Stackoverflow.com