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 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>
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: