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:= exprSELECT 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 |
+------+