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
-
4under 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– longneck2009-09-16 16:19:50 +00:00Commented Sep 16, 2009 at 16:19
-
3I 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– Mike2009-09-16 16:48:40 +00:00Commented Sep 16, 2009 at 16:48
-
1@longneck, Ask the 16k visitors on this page, plus the 67k visitors on stackoverflow.com/q/12176709/632951 for real-world circumstances whereby you would do this.Pacerier– Pacerier2015-04-16 06:13:16 +00:00Commented Apr 16, 2015 at 6: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– Alan Stewart2020-05-07 00:26:11 +00:00Commented May 7, 2020 at 0:26
Add a comment
|
2 Answers
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.
Could you use something like MySQL SET? instead of looping through you could store the values in a SET.
5 Comments
Mike
No I can't, I need to insert values in different rows. I want to do this to have a script easy to use.
Phill Pafford
Use PHP (or something) and loop through each of the array and just do a simple insert then
Phill Pafford
what format does the values come in? CSV, in a text file, etc...?
Mike
The come from user edit at the beginning of the script. So it's in mysql format.