8

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
TRiG
  • 10,148
  • 7
  • 57
  • 107
Mike
  • 2,354
  • 3
  • 23
  • 37
  • 4
    under what possible real-world circumstances would you want to do this? if the array is from a SELECT statement, then you should just INSERT ... SELECT. if the array is from your application, then you should INSERT INTO EXEMPLE VALUES (@value, 2), (@value,5), (@value,2) ... – longneck Sep 16 '09 at 16:19
  • 3
    I just want to do a maintenance script to add new value. Instead of doing a copy paste of each insert, I would like to create a list at the beginning of the script. People will not have to read all the script to know what to modify, but only the variable at the beginning. – Mike Sep 16 '09 at 16:48
  • 1
    @longneck, Ask the 16k visitors on this page, plus the 67k visitors on http://stackoverflow.com/q/12176709/632951 for real-world circumstances whereby you would do this. – Pacerier Apr 16 '15 at 06:13
  • How can it be 2020 and mySQL still doesn't have something as simple as data arrays for programmers to use in their sprocs? Don't get me wrong, I'm loving that mySQL has JSON support, but even accessing arrays in JSON is *extremely* clunky. – Alan Stewart May 07 '20 at 00:26

2 Answers2

3

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • I don't mind to use a cursor, but values need to come from something like this SET @myValues [2,34,22]. Thanx – Mike Sep 16 '09 at 17:03
  • Thanx, it's what I was looking for. The syntax is too ugly to use but it anwsered my question. – Mike Sep 16 '09 at 17:24
0

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

Phill Pafford
  • 83,471
  • 91
  • 263
  • 383