Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Assign values to different types of variables. Learn the syntax for setting user-defined variables, system variables, and stored program variables.
One can also set a user variable in any expression with this syntax:
The SET statement assigns values to different types of variables that affect the operation of the server or your client.
The SET statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed SET OPTION, but this syntax was deprecated in favor of SET without OPTION.
Changing a system variable by using the SET statement does not make the change permanently. To do so, the change must be made in a .
For setting variables on a per-query basis, see .
See for documentation on viewing server system variables.
See for a list of all the system variables.
When setting a system variable, the scope can be specified as either GLOBAL or SESSION.
A global variable change affects all new sessions. It does not affect any currently open sessions, including the one that made the change.
A session variable change affects the current session only.
If the variable has a session value, not specifying either GLOBAL or SESSION will be the same as specifying SESSION. If the variable only has a global value, not specifying GLOBAL or SESSION will apply to the change to the global value.
Setting a global variable to DEFAULT will restore it to the server default, and setting a session variable to DEFAULT will restore it to the current global value.
is a global variable.
is a session variable.
is both global and session.
Setting the session values:
Setting the global values:
will by default return the session value unless the variable is global only.
Using the inplace syntax:
This page is licensed: GPLv2, originally from
SET variable_assignment [, variable_assignment] ...
variable_assignment:
user_var_name = expr
| [GLOBAL | SESSION] system_var_name = expr
| [@@global. | @@session. | @@]system_var_name = expruser_var_name:= exprAssign values to system variables. Learn to use the SET statement to configure GLOBAL and SESSION variables for tuning server behavior.
SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT | 64 | 64 |
| SKIP_PARALLEL_REPLICATION | OFF | NULL |
| INNODB_SYNC_SPIN_LOOPS | NULL | 30 |
+---------------------------+---------------+--------------+SET max_error_count=128;Query OK, 0 rows affected (0.000 sec)
SET skip_parallel_replication=ON;Query OK, 0 rows affected (0.000 sec)
SET innodb_sync_spin_loops=60;
ERROR 1229 (HY000): Variable 'innodb_sync_spin_loops' is a GLOBAL variable
and should be set with SET GLOBAL
SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT | 128 | 64 |
| SKIP_PARALLEL_REPLICATION | ON | NULL |
| INNODB_SYNC_SPIN_LOOPS | NULL | 30 |
+---------------------------+---------------+--------------+SET GLOBAL max_error_count=256;
SET GLOBAL skip_parallel_replication=ON;
ERROR 1228 (HY000): Variable 'skip_parallel_replication' is a SESSION variable
and can't be used with SET GLOBAL
SET GLOBAL innodb_sync_spin_loops=120;
SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT | 128 | 256 |
| SKIP_PARALLEL_REPLICATION | ON | NULL |
| INNODB_SYNC_SPIN_LOOPS | NULL | 120 |
+---------------------------+---------------+--------------+SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_error_count | 128 |
+-----------------+-------+
SHOW VARIABLES LIKE 'skip_parallel_replication';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| skip_parallel_replication | ON |
+---------------------------+-------+
SHOW VARIABLES LIKE 'innodb_sync_spin_loops';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_sync_spin_loops | 120 |
+------------------------+-------+SELECT (@a:=1);
+---------+
| (@a:=1) |
+---------+
| 1 |
+---------+
SELECT @a;
+------+
| @a |
+------+
| 1 |
+------+Assign values to user-defined variables. This guide explains how to store data in session-specific variables for reuse in subsequent SQL statements.
SET var_name = expr [, var_name = expr] ...The SET statement in is an extended version of the general statement. Referenced variables may be ones declared inside a stored program, global system variables, or user-defined variables.
The SET statement in stored programs is implemented as part of the pre-existing syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables, global and session server variables, user-defined variables) can be mixed. This also allows combinations of local variables and some options that make sense only for system variables; in that case, the options are recognized but ignored.
SET can be used with both and .
When setting several variables using the columns returned by a query, should be preferred.
To set many variables to the same value, the function can be used.
Below is an example of how a user-defined variable may be set:
This page is licensed: GPLv2, originally from
This maps all strings sent between the current client and the server with the given mapping.
This page is licensed: CC BY-SA / Gnu FDL
SET @x = 1;SET {CHARACTER SET | CHARSET}
{charset_name | DEFAULT}SHOW VARIABLES LIKE 'character_set\_%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+--------+
SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
SET CHARACTER SET utf8mb4;
SHOW VARIABLES LIKE 'character_set\_%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| character_set_client | utf8mb4 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+---------+
SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+Set a system variable for the duration of a single query. This statement allows temporary configuration changes that apply only to the immediate statement.
var is a system variable (list of allowed variables is provided below), and value is a constant literal.
SET STATEMENT var1=value1 FOR stmt
is roughly equivalent to
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 :
One can switch on/off individual optimizations:
It is possible to enable MRR/BKA for a query:
Note that it makes no sense to try to set a session variable inside a SET STATEMENT:
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:
autocommit
character_set_client
character_set_connection
character_set_filesystem
The feature was originally implemented as a Google Summer of Code 2009 project by Joseph Lukas.
Percona Server 5.6 included it as
MariaDB ported the patch and fixed many bugs. The task in MariaDB Jira is .
This page is licensed: CC BY-SA / Gnu FDL
SET STATEMENT var1=value1 [, var2=value2, ...]
FOR <statement>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
SET @save_value=@@var1;
SET SESSION var1=value1;
stmt;
SET SESSION var1=@save_value;SET STATEMENT max_statement_time=1000 FOR SELECT ... ;SET STATEMENT optimizer_switch='materialization=off' FOR SELECT ....;SET STATEMENT join_cache_level=6, optimizer_switch='mrr=on' FOR SELECT ...#USELESS STATEMENT
SET STATEMENT sort_buffer_size = 100000 FOR SET SESSION sort_buffer_size = 200000;This determines which character set the client will use to send statements to the server, and the server will use for sending results back to the client.
ucs2, utf16, utf16le and utf32 are not valid character sets for SET NAMES, as they cannot be used as client character sets.
The collation clause is optional. If not defined (or if DEFAULT is specified), the default collation for the character set will be used.
Quotes are optional for the character set or collation clauses.
utf8mb4 is the default for the affected variables:
SET NAMES DEFAULT;
SELECT VARIABLE_NAME, SESSION_VALUE
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME LIKE 'character_set_con%' OR
VARIABLE_NAME LIKE 'character_set_cl%' OR
VARIABLE_NAME LIKE
The utf8 character set (and related collations) is an alias for utf8mb3 , rather than the other way around. MariaDB 11.4 added the character_set_collations variable, so the SELECT query is more specific in this example:
SELECT VARIABLE_NAME, SESSION_VALUE
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME LIKE 'character_set_con%' OR
VARIABLE_NAME LIKE 'character_set_cl%' OR
VARIABLE_NAME LIKE 'character_set_re%' OR
VARIABLE_NAME
The utf8 character set (and related collation) is the default for the given variables:
This page is licensed: CC BY-SA / Gnu FDL
Define isolation levels and access modes for transactions. Learn to configure the behavior of the next transaction or the entire session for data consistency.
SELECT VARIABLE_NAME, SESSION_VALUE
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME LIKE 'character_set_c%' OR
VARIABLE_NAME LIKE 'character_set_re%' OR
VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+-----------------+
| VARIABLE_NAME | SESSION_VALUE |
+--------------------------+-----------------+
| CHARACTER_SET_RESULTS | utf8 |
| CHARACTER_SET_CONNECTION | utf8 |
| CHARACTER_SET_CLIENT | utf8 |
| COLLATION_CONNECTION | utf8_general_ci |
+--------------------------+-----------------+
SET NAMES big5;
SELECT VARIABLE_NAME, SESSION_VALUE
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME LIKE 'character_set_c%' OR
VARIABLE_NAME LIKE 'character_set_re%' OR
VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+-----------------+
| VARIABLE_NAME | SESSION_VALUE |
+--------------------------+-----------------+
| CHARACTER_SET_RESULTS | big5 |
| CHARACTER_SET_CONNECTION | big5 |
| CHARACTER_SET_CLIENT | big5 |
| COLLATION_CONNECTION | big5_chinese_ci |
+--------------------------+-----------------+
SET NAMES 'latin1' COLLATE 'latin1_bin';
SELECT VARIABLE_NAME, SESSION_VALUE
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME LIKE 'character_set_c%' OR
VARIABLE_NAME LIKE 'character_set_re%' OR
VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+---------------+
| VARIABLE_NAME | SESSION_VALUE |
+--------------------------+---------------+
| CHARACTER_SET_RESULTS | latin1 |
| CHARACTER_SET_CONNECTION | latin1 |
| CHARACTER_SET_CLIENT | latin1 |
| COLLATION_CONNECTION | latin1_bin |
+--------------------------+---------------+
SET NAMES DEFAULT;
SELECT VARIABLE_NAME, SESSION_VALUE
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
VARIABLE_NAME LIKE 'character_set_c%' OR
VARIABLE_NAME LIKE 'character_set_re%' OR
VARIABLE_NAME LIKE 'collation_c%';
+--------------------------+-------------------+
| VARIABLE_NAME | SESSION_VALUE |
+--------------------------+-------------------+
| CHARACTER_SET_RESULTS | latin1 |
| CHARACTER_SET_CONNECTION | latin1 |
| CHARACTER_SET_CLIENT | latin1 |
| COLLATION_CONNECTION | latin1_swedish_ci |
+--------------------------+-------------------+SET NAMES {'charset_name'
[COLLATE 'collation_name'] | DEFAULT}With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected.
With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session.
Without any SESSION or GLOBAL keyword, the statement sets the isolation level for only the next (not started) transaction performed within the current session. After that it reverts to using the session value.
A change to the global default isolation level requires the SUPER privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction.
To set the global default isolation level at server startup, use the --transaction-isolation=level option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the allowable values are READ_UNCOMMITTED,READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mariadb] section of an option file:
To determine the global and session transaction isolation levels at runtime, check the value of the transaction_isolation variable.
To determine the global and session transaction isolation levels at runtime, check the value of the tx_isolation system variable.
InnoDB supports each of the translation isolation levels described here using different locking strategies. The default level isREPEATABLE READ. For additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see InnoDB Lock Modes, and innodb-locks-set.html.
The following sections describe how MariaDB supports the different transaction levels.
SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a "dirty
read". Otherwise, this isolation level works likeREAD COMMITTED.
A somewhat Oracle-like isolation level with respect to consistent (non-locking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See innodb-consistent-read.html.
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus allows the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because "phantom rows" must be blocked for MariaDB replication and recovery to work.
This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the
snapshot established by the first read. This convention means that if you issue several plain (non-locking) SELECT statements within the same transaction, these SELECT statements are consistent
also with respect to each other. See innodb-consistent-read.html.
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. MariaDB does not relax the gap locking for unique indexes.
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.
This is the minimum isolation level for non-distributed XA transactions.
This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (non-locking) read and need not block for other transactions. (This means that to force a plain SELECT to block if other transactions have modified the selected rows, you should disable autocommit.)
Distributed XA transactions should always use this isolation level.
If the innodb_snapshot_isolation system variable is not set to ON, strictly-speaking anything other than READ UNCOMMITTED is not clearly defined. innodb_snapshot_isolation defaults to OFF for backwards compatibility. Setting to ON will result in attempts to acquire a lock on a record that does not exist in the current read view raising an error, and the transaction being rolled back.
If the innodb_snapshot_isolation system variable is not set to ON, strictly-speaking anything other than READ UNCOMMITTED is not clearly defined.
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the tx_read_only system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global read_only mode, the READ_ONLY ADMIN privilege doesn't allow writes, and DDL statements on temporary tables are not allowed either.
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the tx_read_only system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global read_only mode, the SUPER privilege doesn't allow writes, and DDL statements on temporary tables are not allowed either.
It is not permitted to specify both READ WRITE and READ ONLY in the same statement.
READ WRITE and READ ONLY can also be specified in the START TRANSACTION statement, in which case the specified mode is only valid for one transaction.
Attempting to set the isolation level within an existing transaction without specifying GLOBAL or SESSION.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET [GLOBAL | SESSION] TRANSACTION
transaction_property [, transaction_property] ...
transaction_property:
ISOLATION LEVEL level
| READ WRITE
| READ ONLY
level:
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE[mariadb]
transaction-isolation = REPEATABLE-READSELECT @@GLOBAL.transaction_isolation, @@tx_isolation;SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progressWith both READ UNCOMMITTED and READ COMMITTED isolation levels, you can’t expect results to be deterministic between successive statements of the same transaction.
With both READ UNCOMMITTED and READ COMMITTED isolation levels, you can’t expect results to be deterministic between successive statements of the same transaction.
Enable or disable binary logging for the current session. This statement allows administrators to perform operations without replicating them to replicas.
SET [SESSION] sql_log_bin = {0|1}Sets the sql_log_bin system variable, which disables or enables binary logging for the current connection, if the client has the SUPER . The statement is refused with an error if the client does not have that privilege.
Note that setting sql_log_bin=1 has no effect if variable, which enables global binary logging, is not set.
You cannot set sql_log_bin as a global variable.
You can set sql_log_bin as a global variable. This is considered dangerous, though, as it can damage replication.
This page is licensed: GPLv2, originally from