将数组传递给 MySQL 存储例程

我需要将一个字符串数组作为参数传递给一个 MySQL 存储例程。数组可能很长，而且它的元素数量不固定。然后我想将字符串值放入一个只有一列的内存表中，这样我就可以处理数据了。我不知道这是否可以在 MySQL 中完成。也许需要一些肮脏的变通方法。

例如，我有字符串值:

Banana, Apple, Orange

现在我想从我的 MySQL Fruits表中获得这些水果的数据。伪代码:

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 允许您使用 TEXT数据类型并将数组作为 XML 字符串提交，从而快速创建内存中的表。然而，我不认为这种技术在 MySQL 中是可行的。

如果有任何帮助，我们将不胜感激！

150988 次浏览
小开
最佳答案

您可以使用列表传递一个字符串，并使用 事先准备好的陈述运行查询，例如-

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 ;

使用方法:

SET @fruitArray = '\'apple\',\'banana\'';
CALL GetFruits(@fruitArray);
小开

我想出了一个尴尬但实用的解决方案。它适用于一维数组(更多的维数将是棘手的)和适合于 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;

例如，此代码的输入可以是字符串 Apple|Banana|Orange。MyTemporaryTable将分别用包含字符串 Apple、 Banana和 Orange的三行填充。

我认为字符串处理的缓慢速度会使这种方法毫无用处，但是它足够快了(对于1,000个条目数组来说，只需要几分之一秒的时间)。

希望这对谁有帮助。

小开

像这样简单地使用 FIND _ IN _ SET:

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2

所以你可以这样做:

select * from Fruits where FIND_IN_SET(fruit, fruitArray) > 0
小开

这模拟了一个字符数组，但是您可以用 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;
小开

使用临时表连接。不需要将临时表传递给函数 它们是全球性的。

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
小开

如果你不想使用临时表，这里有一个类似拆分字符串的函数你可以使用

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)返回第一个 n
  • SUBSTRING_INDEX(string, delim, -1)只返回最后一个
  • REGEXP '((delim).*){n}'检查是否有 n 个分隔符(也就是说，您处于界限内)
小开

这有助于我做 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//;

来电:

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

我不确定这是否完全回答了这个问题(它不是) ，但它是我为我的类似问题想出的解决方案。在这里，我只尝试在每个分隔符中使用一次 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');
小开

如果您的查询集是一个有限范围的整数(例如，max 100) ，并且您需要在复杂的查询中获得更好的性能，我从上面的答案中得到了一个扩展的解决方案:

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)

提交答案