MySQL procedure with array parameter

In this article I want to explain how you can implement a MySQL procedure with an array parameter. This means a procedure that processes an array of values instead of scalar values. An example is processing a set of records whose number is not previously known.
Another example is when you have two tables A and B with a one-to-many relationship. For every ID in a given set of records from A, you want to execute a bulk insert of related records in the B table.

So your MySQL procedure should ideally have an input parameter of type array. However if you read the MySQL documentation, you will discover that MySQL (at least as of version 5.7) does not support procedure parameters of type array.

Since MySQL does not support parameters of type array, what is a possible workaround?

Let’s imagine that you want to process an array of IDs, each one being the key of a record. The solution I found is to use a parameter of type VARCHAR. This means that you will pass the list of IDs to your procedure as a string containing the IDs separated by commas. This will certainly limit the number of IDs to those that fit in the maximum length declared for your VARCHAR parameter. However, since VARCHAR fields can have a length of up to 65535 characters, you can process many IDs at once. For example if your IDs are 7 digits long, you will need 8 characters for every ID (7 digits plus the comma separator). This means that in this case you can process up to 65535/8 IDs , roughly 8200.

Once you have declared the procedure parameter as a VARCHAR, how should you process it?

• If the statement to execute is simple, you can use it in an IN clause
• however you could have to extract every ID from the string and for each one build a more complex statement. Or maybe you must process the IDs in a loop one by one.

In the second case the solution I found has been to parse the input parameter with MySQL string functions.

Here you can find a code example for the procedure.

CREATE PROCEDURE `MANAGE_ID_LIST`(
        IN id_array VARCHAR(1000)
    )
    READS SQL DATA
    SQL SECURITY INVOKER

BEGIN
    DECLARE id_array_local VARCHAR(1000);
    DECLARE start_pos SMALLINT;
    DECLARE comma_pos SMALLINT;
    DECLARE current_id VARCHAR(1000);
    DECLARE end_loop TINYINT;

    SET id_array_local = id_array;
    SET start_pos = 1;
    SET comma_pos = locate(',', id_array_local);

    REPEAT
        IF comma_pos > 0 THEN
            SET current_id = substring(id_array_local, start_pos, comma_pos - start_pos);
            SET end_loop = 0;
        ELSE
            SET current_id = substring(id_array_local, start_pos);
            SET end_loop = 1;
        END IF;
     

        # Place here your code that uses current_id
       

        IF end_loop = 0 THEN
            SET id_array_local = substring(id_array_local, comma_pos + 1);
            SET comma_pos = locate(',', id_array_local);
        END IF;
    UNTIL end_loop = 1

    END REPEAT;
END

This procedure parses the input parameter id_array and extracts the IDs one by one with a REPEAT … UNTIL loop. At every iteration the current_id variable holds the extracted ID. Then you can write the code that uses this ID where you see the comment line.