How can I simulate an array variable in MySQL?

Question

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 1

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.)

Answer 2

You can achieve this in MySQL using WHILE loop:

SET @myArrayOfValue = '2,5,2,23,6,';  WHILE (LOCATE(',', @myArrayOfValue) > 0) DO     SET @value = ELT(1, @myArrayOfValue);     SET @myArrayOfValue= SUBSTRING(@myArrayOfValue, LOCATE(',',@myArrayOfValue) + 1);      INSERT INTO `EXEMPLE` VALUES(@value, 'hello'); END WHILE; 

EDIT: Alternatively you can do it using UNION ALL:

INSERT INTO `EXEMPLE` (  `value`, `message` ) (  SELECT 2 AS `value`, 'hello' AS `message`  UNION ALL  SELECT 5 AS `value`, 'hello' AS `message`  UNION ALL  SELECT 2 AS `value`, 'hello' AS `message`  UNION ALL  ... );