SET

You are viewing an old version of this article. View the current version here.

Syntax

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

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 |
+------+

See Also

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.