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 [, var2=value2, ...] FOR <statement>
where varN
is a system variable (list of allowed variables is provided below), and valueN
is a constant literal.
Description
SET STATEMENT var1=value1 FOR stmt
is equivalent to
SET @save_value=@@var1; SET SESSION var1=value1; stmt; SET SESSION var1=@save_value;
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.
Use cases
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.
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
- 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
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, max_statement_time=100 FOR SELECT field1 FROM table_name ORDER BY field1;
Source
The feature was based on a Google Summer of Code 2009 project, implementation by Joseph Lukas. Further patches by Percona. See also MDEV-5231.