{"id":4002,"date":"2011-12-02T06:59:03","date_gmt":"2011-12-02T04:59:03","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4002"},"modified":"2011-12-02T13:44:06","modified_gmt":"2011-12-02T11:44:06","slug":"mysql-foreach","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-foreach","title":{"rendered":"MySQL foreach()"},"content":{"rendered":"

A new routine is now available in common_schema<\/a>, which makes for an easier execution syntax for some operations:<\/p>\n

\n
foreach(collection_to_iterate_over<\/em><\/span>, queries_to_execute_per_iteration_step<\/em><\/span>);<\/pre>\n<\/blockquote>\n
To illustrate what it can do, consider:<\/p>\n
\n
call foreach('table in sakila'<\/span>, 'ALTER TABLE ${schema}<\/strong>.${table}<\/strong> ENGINE=InnoDB ROW_FORMAT=COMPACT'<\/span>);\r\n\r\ncall $('schema like shard_%<\/span>', 'CREATE TABLE ${schema}<\/strong>.messages (id INT)'<\/span>);\r\n\r\ncall $('2000:2009'<\/span>, 'INSERT IGNORE INTO report (report_year) VALUES (${1}<\/strong>)'<\/span>);<\/pre>\n<\/blockquote>\n
$()<\/strong> stands as a synonym to foreach()<\/em>. I suspect it should look familiar to web programmers.<\/p>\n
The idea for foreach()<\/em> was introduced by Giuseppe Maxia during a correspondence. At first I was skeptic: this isn’t jQuery<\/a>; this is SQL. Why would I want to use foreach()<\/em>?<\/p>\n
Then Giuseppe provided some use cases, and as I started thinking about it, I found more and more cases where such a tool might considerably reduce scripting overhead and avoid requiring SQL-fu skills. In fact, I have been using it myself for the past few weeks<\/p>\n
I provide examples which I hope will convince the reader as for the simplicity of using such syntax. Showing off the types of input foreach()<\/em> accepts (query, table search, schema search, set of constants, single or double numbers range), and the types of queries it can execute (single, multiple, using placeholders).<\/p>\n
I stress that this is not a replacement for common queries (i.e. does not<\/em> come to replace your common SELECT<\/strong>, UPDATE<\/strong>, DELETE<\/strong>), but more for working out administrative tasks. Nevertheless, the last example in this post does provide with an interesting insight on possible “normal” use.<\/p>\n
Use case: using values from query<\/h4>\n
Let’s kill all queries running for over 20<\/strong> seconds:<\/p>\n
\n
call foreach('SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time > 20'<\/span>, 'KILL QUERY ${1}<\/strong>'<\/span>);<\/pre>\n<\/blockquote>\n
The thing I like most about foreach()<\/em> is that it’s self explanatory. Nevertheless, I note:<\/p>\n