SET variable_assignment [, variable_assignment] ... variable_assignment: user_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@@global. | @@session. | @@]system_var_name = expr
One can also set a user variable in any expression with this syntax:
user_var_name:= expr
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
, and was removed in MariaDB 10.0.
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 configuration file.
For setting variables on a per-query basis (from MariaDB 10.1.2), see SET STATEMENT
.
See SHOW VARIABLES
for documentation on viewing server system variables.
See Server System Variables 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.
SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max_error_count' OR VARIABLE_NAME LIKE 'skip_parallel_replication' OR VARIABLE_NAME LIKE '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 | +---------------------------+---------------+--------------+
Setting the session values:
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 LIKE 'max_error_count' OR VARIABLE_NAME LIKE 'skip_parallel_replication' OR VARIABLE_NAME LIKE '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 | +---------------------------+---------------+--------------+
Setting the global values:
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 LIKE 'max_error_count' OR VARIABLE_NAME LIKE 'skip_parallel_replication' OR VARIABLE_NAME LIKE '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 will by default return the session value unless the variable is global only.
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 | +------------------------+-------+
Using the inplace syntax:
SELECT (@a:=1); +---------+ | (@a:=1) | +---------+ | 1 | +---------+ SELECT @a; +------+ | @a | +------+ | 1 | +------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/set/