将数组传递给 MySQL 存储例程

2021-11-18 00:00:00 arrays parameter-passing mysql stored-procedures

我需要将字符串数组作为参数传递给 MySQL 存储例程.该数组可能很长，并且其元素数量不固定.然后我想将字符串值放入一个包含一列的内存表中，以便我可以处理数据.我不知道这是否可以在 MySQL 中完成.也许需要一些肮脏的解决方法.

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.

例如，我有字符串值:

Banana, Apple, Orange

现在我想从我的 MySQL Fruits 表中获取有关这些水果的数据.伪代码:

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

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!

推荐答案

您可以在列表中传递一个字符串并使用 prepared statements 运行查询，例如-

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 ;

使用方法:

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

相关文章