How can I simulate an array variable in MySQL?


It appears that MySQL doesn't have array variables. What should I use instead?


There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to use these instead of array variables? Alternatively, if I go with sets, what would be the set-based idiom equivalent to foreach?


Answers:


Well, I've been using temporary tables instead of array variables. Not the greatest solution, but it works.

Note that you don't need to formally define their fields, just create them using a SELECT:

CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table
SELECT first_name FROM people WHERE last_name = 'Smith';

(See also Create temporary table from select statement without using Create Table.)