Passing Arrays to MySQL Stored Routines

MySQL does not have an array type that can be used in table definitions which precludes a programmer from using arrays in Stored Routines as well. This can be troublesome when a group of values need to be passed. I wrote a few functions and a stored routine example below to handle these situations. The basis of it all is to use strings of separated values and regular expressions to validate.

Array Definition

A separated list of values as a string. For this post, I will use a comma as the separator, but this can be whatever works for your specific application. I define what a value is by a REGEX. For this post, I will use 3 digit unsigned integers ‘^[1-9][0-9]{2}$’. An array is 0 or more values, so the array could be validated by an empty string comparison and the REGEX ‘^([1-9][0-9]{2},)*[1-9][0-9]{2}?$’. An example of an array matching these criteria is “123,534,657,100,240,243”.

Array Formatting

When this array is passed to a stored routine, I first pass it through a function to format it. I allow for space anywhere around values so a user can enter it in a more user-friendly way without affecting the integrity of the data. Below is the function definition. CSL stands for Comma Separated List.

CREATE FUNCTION formatCSL(
_text TEXT
)
RETURNS TEXT
NO SQL
SQL SECURITY DEFINER
BEGIN

IF _text IS NULL THEN
    RETURN NULL;
END IF;

SET _text = TRIM(_text);

WHILE INSTR(_text, ' ,') DO
    SET _text = REPLACE(_text, ' ,', ',');
END WHILE;

WHILE INSTR(_text, ', ') DO
    SET _text = REPLACE(_text, ', ', ',');
END WHILE;

RETURN _text;

END

Array Validation function

Now that the array is formatted, it needs to be validated. Validation is simple with REGEXP. This validation function makes it much easier to write code to break it apart later because proper format can be assumed.

CREATE FUNCTION isValidCSL(
_textIn TEXT
)
RETURNS BOOLEAN
NO SQL
SQL SECURITY DEFINER
BEGIN

RETURN _textIn IS NOT NULL && (_textIn = '' || _textIn REGEXP '^([1-9][0-9]{2},)*[1-9][0-9]{2}?$');

END

Parsing the passed array

Now we know we have a valid list of values and can begin iterating through them in the stored procedure. The inner part of the parsing is application specific. Depending on how large the array is and what you are doing with them, it may make sense to insert them into a temporary table and then do any calculations or manipulations from there. Below is a very basic, generalized loop I use for pulling each value out of the array. I used session variables to highlight them.

SET @separator = ',';
SET @separatorLength = CHAR_LENGTH(@separator);

WHILE _passedArray != '' > 0 DO
    SET @currentValue = SUBSTRING_INDEX(_passedArray, @separator, 1);

    -- DO WORK

    SET _passedArray = SUBSTRING(_passedArray, CHAR_LENGTH(@currentValue) + @separatorLength + 1);
END WHILE;

Hopefully this will help solve some issues with passing arrays to stored procedures in MySQL. These functions and skeleton code are reused throughout our applications and has helped us create a lot of functionality with the basic SQL types.

{o}
Otreva is a custom software product development company focusing on user experience, responsive web development, & mobile application development.

Interested in seeing what it costs to build an app?

Start Quote See Stats
224 Wyoming Ave. #100
Scranton, PA 18503

You've found the secret footer!

Tweets about @Otreva

Or check us out on:

Shopify Experts
Awwwards