How Can I Pass Arrays to MySQL Stored Procedures?

Passing Arrays to MySQL Stored Routines

A common requirement is passing arrays of values as parameters to MySQL stored routines. However, unlike many other database management systems, MySQL does not natively support this functionality.

One viable solution is to convert the array into a string and pass it as an argument. This string can then be used within the stored routine to create a temporary table using the CONCAT() and SELECT INTO statements:

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 ;

By passing the stringified array into the GetFruits procedure, you can dynamically create a temporary table containing the specified fruit names, enabling further processing within your script.

To utilize this procedure, you can assign the array of strings to a MySQL variable and then invoke the stored routine:

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

This approach effectively allows you to pass an array of values to a stored routine in MySQL, facilitating complex database operations that involve variable-length string lists.

The above is the detailed content of How Can I Pass Arrays to MySQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!