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 [, 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.

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.