SET STATEMENT

You are viewing an old version of this article. View the current version here.
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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.