SET STATEMENT
Contents
MariaDB starting with 10.1.2
Per-query variables were introduced in MariaDB 10.1.2
SET STATEMENT
can be used to set the value of system variable (or multiple variables) for the duration of the statement.
Syntax
SET STATEMENT var1=value1 [, var=value2, ...] FOR <statement>
Description
Sets value of system variable for the duration of the statement. It is also possible to set multiple system variables.
SET STATEMENT var1=value1 FOR stmt
is equivalent to
SET @save_value=@@var1; SET SESSION var1=value1; stmt; SET SESSION var1=@save_value;
This can also be used to limit the execution time of an individual query in conjunction with the max_statement_time variable. A comma-delimited list can be used to set multiple values at the same time.
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
The server first 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.
SET STATEMENT <variable=value> FOR <statement>
in general works the same as running
SET SESSION var=val; statement; SET SESSION var=old_val
This means that examining @@session.var
inside the statement will return the new value, while the old value will be restored regardless of changes inside the statement.
Examples
SET STATEMENT sort_buffer_size=200000 FOR SELECT field1 FROM table_name ORDER BY field1;
SET STATEMENT sort_buffer_size=200000, max_statement_time=100 FOR SELECT field1 FROM table_name ORDER BY field1;
The feature was based on a Google Summer of Code 2009 project, implementation by Joseph Lukas. Further patches by Percona. See also MDEV-5231.