How Can I Pass String Arrays to MySQL Stored Procedures?

Passing Array Parameters to MySQL Stored Routines

When working with variable-length arrays of strings, passing them as parameters to MySQL stored routines can pose a challenge. To overcome this, an alternative solution is to concatenate the array values into a single string and utilize prepared statements.

The following code snippet outlines the process:

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 ;

To use this stored procedure, you can initialize a string variable with the array values separated by commas and call the procedure, passing the variable as an argument:

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

This solution effectively allows you to pass an array of string values to a MySQL stored routine and work with the data in an in-memory table.

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