How Can I Pass a String Array to a MySQL Stored Procedure?

Passing an Array to a MySQL Stored Routine

When working with stored routines in MySQL, one may encounter the need to pass an array of strings as a parameter. This task presents challenges due to the lack of an explicit array data type in MySQL. However, by leveraging a dynamic SQL technique, we can achieve this functionality.

Solution: Constructing a Dynamic SQL Query

To pass an array to a stored routine, we can employ dynamic SQL, where we construct a SQL query at runtime using string concatenation. The following stored procedure demonstrates this approach:

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 ;

In this stored procedure:

• fruitArray: The input parameter to receive the comma-separated list of strings.
• @sql: A variable to hold the dynamically constructed SQL query.
• PREPARE: Prepares the dynamic SQL query for execution.
• EXECUTE: Executes the prepared query.
• DEALLOCATE PREPARE: Releases the prepared query from memory.

Usage:

To utilize the stored procedure, we can call it and pass the array of strings as follows:

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

This approach allows us to dynamically create and execute a query based on the provided array, providing a workaround for the lack of a native array data type in MySQL.

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