SET STATEMENT

Syntax

var is a system variable (list of allowed variables is provided below), and value is a constant literal.

Description

SET STATEMENT var1=value1 FOR stmt

is roughly equivalent to

The server parses the whole statement before executing it, so any variables set in this fashion that affect the parser may not have the expected effect. Examples include the charset variables, sql_mode=ansi_quotes, etc.

Examples

One can limit statement execution time :

One can switch on/off individual optimizations:

It is possible to enable MRR/BKA for a query:

Note that it makes no sense to try to set a session variable inside a SET STATEMENT:

For the above, after setting sort_buffer_size to 200000 it will be reset to its original state (the state before the SET STATEMENT started) after the statement execution.

Limitations

There are a number of variables that cannot be set on per-query basis. These include:

• autocommit

• character_set_client

• character_set_connection

• character_set_filesystem

Source

• The feature was originally implemented as a Google Summer of Code 2009 project by Joseph Lukas.

• Percona Server 5.6 included it as

• MariaDB ported the patch and fixed many bugs. The task in MariaDB Jira is .

_{This page is licensed: CC BY-SA / Gnu FDL}