SET
Syntax
SET variable_assignment [, variable_assignment] ... variable_assignment: user_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@@global. | @@session. | @@]system_var_name = expr
Contents
One can also set a user variable in any expression with this syntax:
user_var_name:= expr
Description
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.
GLOBAL / SESSION
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.
DEFAULT
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.
Examples
SHOW VARIABLES WHERE Variable_name LIKE "aria_group_commit%"; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | aria_group_commit | none | | aria_group_commit_interval | 0 | +----------------------------+-------+ SET GLOBAL aria_group_commit="HARD"; SET GLOBAL aria_group_commit_interval=100; SHOW VARIABLES WHERE Variable_name LIKE "aria_group_commit%"; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | aria_group_commit | hard | | aria_group_commit_interval | 100 | +----------------------------+-------+
Using the inplace syntax:
SELECT (@a:=1); +---------+ | (@a:=1) | +---------+ | 1 | +---------+ SELECT @a; +------+ | @a | +------+ | 1 | +------+