execution_routines

SYNOPSIS

Execution routines: stored routines managing dynamic query execution, iteration & evaluation.

  • eval(): Evaluates the queries generated by a given query.
  • exec(): Executes a given query or semicolon delimited list of queries.
  • exec_file():Executes queries from given file, residing on server./li>
  • exec_single(): Executes a given query.
  • foreach() aka $(): Invoke script on each element of given collection.
  • repeat_exec(): Repeatedly executes given query or queries until some condition holds.
  • run(): run a QueryScript code provided as text.
  • run_file(): run a QueryScript code from file.
  • script_runtime(): number of seconds elapsed since script execution began.
  • throw(): Disrupt execution with error.

DESCRIPTION

These featured routines allow for scripting & semi-scripting capabilities in MySQL. Looping through collections, row sets, numbers, tables; repeating tasks until a given condition hold, or dynamically evaluating queries. The execution routines simplify a DBA's maintenance work by providing with a simpler, cleaner and more familiar syntax.

The run() and run_file() routines execute QueryScript code. The rest of the routines make for lower level, semi-scripting execution.

The majority of operations in these routines use dynamic queries, based on prepared statements. Note that MySQL does not support invoking a prepared statement from within a prepared statement. This means you may wish to avoid calling on these routines using prepared statements code (some frameworks will, by default, invoke queries using prepared statements regardless of the query type).

EXAMPLES

Use foreach() to convert sakila tables to InnoDB: 

mysql> call foreach(
	  'table in sakila', 
	  'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPACT');

Use repeat_exec() to delete huge amount of rows in smaller chunks, with sleeping interval: 

mysql> call repeat_exec(2, 
	  'DELETE FROM sakila.rental WHERE customer_id=7 ORDER BY rental_id LIMIT 1000', 
	  0);

Use eval() to kill transactions being idle for over 30 seconds: 

mysql> call eval("SELECT sql_kill_query FROM common_schema.innodb_transactions WHERE trx_idle_seconds > 30");

 
common_schema documentation