Per-query variables were introduced in MariaDB 10.1.2
SET STATEMENT
can be used to set the value of a system variable for the duration of the statement. It is also possible to set multiple variables.
SET STATEMENT var1=value1 [, var2=value2, ...] FOR <statement>
where varN
is a system variable (list of allowed variables is provided below), and valueN
is a constant literal.
SET STATEMENT var1=value1 FOR stmt
is roughly equivalent to
SET @save_value=@@var1; SET SESSION var1=value1; stmt; SET SESSION var1=@save_value;
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.
One can limit statement execution time max_statement_time
:
SET STATEMENT max_statement_time=1000 FOR SELECT ... ;
One can switch on/off individual optimizations:
SET STATEMENT optimizer_switch='materialization=off' FOR SELECT ....;
It is possible to enable MRR/BKA for a query:
SET STATEMENT join_cache_level=6, optimizer_switch='mrr=on' FOR SELECT ...
Note that it makes no sense to try to set a session variable inside a SET STATEMENT
:
#USELESS STATEMENT SET STATEMENT sort_buffer_size = 100000 for SET SESSION sort_buffer_size = 200000;
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.
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
collation_connection
default_master_connection
debug_sync
interactive_timeout
gtid_domain_id
last_insert_id
log_slow_filter
log_slow_rate_limit
log_slow_verbosity
long_query_time
min_examined_row_limit
profiling
profiling_history_size
query_cache_type
rand_seed1
rand_seed2
skip_replication
slow_query_log
sql_log_off
tx_isolation
wait_timeout
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/set-statement/