Array variable in mysql

Question

Is there a way in a mysql script to declare an array(or any collection) and looping over it for doing stuff.

Ex :

SET @myArrayOfValue=[2,5,2,23,6]


for each @value in @myArrayOfValue
   INSERT INTO EXEMPLE VALUES(@value, 'hello');
end for each

Thanx in advance.

Answer 1

No, SQL does not support FOR EACH/etc syntax. The closest you'd get would be to use cursors. Also, there is no array syntax in SQL - you'd have to use:

SELECT 2 FROM DUAL
UNION ALL
SELECT 34 FROM DUAL
UNION ALL 
SELECT 24 FROM DUAL

... to construct your "array of values" equivalent in SQL.

SQL scripts would have individual INSERT statements. You'd be looking at using PHP/Java/etc. to use FOR loop-esque syntax like what is provided in your example.

Answer 2

Could you use something like MySQL SET? instead of looping through you could store the values in a SET.