将数组传递给 MySQL 存储例程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8149545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pass array to MySQL stored routine
提问by Gruber
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.
我需要将字符串数组作为参数传递给 MySQL 存储例程。该数组可能很长，并且其元素数量不固定。然后我想将字符串值放入一个只有一列的内存表中，这样我就可以处理数据了。我不知道这是否可以在 MySQL 中完成。也许需要肮脏的解决方法。
For example, I have the string values:
例如，我有字符串值：
Banana, Apple, Orange
Now I want to get data on these fruits from my MySQL Fruitstable. Pseudo code:
现在我想从我的 MySQLFruits表中获取有关这些水果的数据。伪代码：
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 TEXTdatatype 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.
Microsoft SQL Server 允许您使用TEXT数据类型并将数组作为 XML 字符串提交，从而快速创建内存表。但是，我认为该技术在 MySQL 中是不可能的。
Any help on how to do this would be appreciated!
任何有关如何做到这一点的帮助将不胜感激！
回答by Devart
You can pass a string with your list and use a prepared statementsto 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);
回答by Sagiv Ofek
Simply use FIND_IN_SETlike that:
只需像这样使用FIND_IN_SET：
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
回答by Raj
This helps for me to do IN condition Hope this will help you..
这有助于我做 IN 条件希望这会帮助你..
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');
回答by bobobobo
Use a join with a temporary table. You don't need to pass temporary tables to functions, they are global.
对临时表使用连接。您不需要将临时表传递给函数，它们是 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
回答by KCD
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)
SUBSTRING_INDEX(string, delim, n)返回第一个 nSUBSTRING_INDEX(string, delim, -1)只返回最后一个REGEXP '((delim).*){n}'检查是否有 n 个分隔符（即您在边界内）
回答by Gruber
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:
我为我的问题想出了一个笨拙但实用的解决方案。它适用于一维数组（更多维度会很棘手）和适合的输入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. MyTemporaryTablewill be populated with three rows containing the strings Apple, Banana, and Orangerespectively.
例如，此代码的输入可以是字符串Apple|Banana|Orange。MyTemporaryTable将用含有字符串三行来填充Apple，Banana和Orange分别。
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).
我认为字符串处理速度慢会使这种方法无用，但它足够快（对于 1,000 个条目的数组只有几分之一秒）。
Hope this helps somebody.
希望这可以帮助某人。
回答by pvilas
This simulates a character array but you can substitute SUBSTR for ELT to simulate a string array
这模拟了一个字符数组，但你可以用 SUBSTR 代替 ELT 来模拟一个字符串数组
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;
回答by Alan Stewart
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.
我不确定这是否完全回答了问题（不是），但这是我为类似问题提出的解决方案。在这里，我尝试对每个分隔符仅使用一次 LOCATE()。
-- *****************************************************************************
-- 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');
