传递数组到MySQL存储程序 [英] Pass array to MySQL stored routine

问题描述

我需要字符串作为参数的数组传递给的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.

例如，我有弦的香蕉的苹果和橙色的。现在，我想从我的MySQL 水果表得到这些水果的数据。伪code：

For example, I have the strings Banana, Apple, and 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的，您可以使用 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.

任何帮助就如何做到这将是AP preciated！

Any help on how to do this would be appreciated!

推荐答案

您可以传递一个字符串列表并使用的$p$ppared声明来运行一个查询，如： -

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);

这篇关于传递数组到MySQL存储程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！