How can I simulate an array variable in MySQL?

mysql arrays variables set temp-tables
einpoklum picture einpoklum · Aug 29, 2012 · Viewed 337.7k times · Source

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?

Answer

einpoklum picture einpoklum · Dec 21, 2012

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:

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

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

Related questions

Passing an array to a query using a WHERE clause

Given an array of ids $galleries = array(1,2,5) I want to have a SQL query that uses the values of the array in its WHERE clause like: SELECT * FROM galleries WHERE id = /* values of array $galleries... eg. (1 || 2 || 5) */ How can I generate …

Read More
Save PHP array to MySQL?

What is a good way to save an array of data to a single mysql field? Also once I query for that array in the mysql table, what is a good way to get it back into array form? Is …

Read More
Pass array to MySQL stored routine

I need to pass an array of strings as parameter to a MySQL stored routine. The array could be long and its number of elements is not fixed. I then want to put the string values into an in-memory table …

Read More