Array variable in mysql

Question

Is there a way in a MySQL script to declare an array (or any collection) and loop over it to do stuff?

For example,

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


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

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.