SET variable_assignment [, variable_assignment] ... variable_assignment: user_var_name = expr | param_name = expr | local_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@@global. | @@session. | @@] system_var_name = expr SET ONE_SHOT system_var_name = expr
syntax for variable assignment enables you to assign values to
different types of variables that affect the operation of the
server or clients:
System variables. See Section 5.1.5, “Server System Variables”. System variables also can be set at server startup, as described in Section 5.1.6, “Using System Variables”. (To display system variable names and values, use the
SHOW VARIABLESstatement; see Section 18.104.22.168, “SHOW VARIABLES Syntax”.)
User-defined variables. See Section 9.4, “User-Defined Variables”.
Stored procedure and function parameters, and stored program local variables. See Section 13.6.4, “Variables in Stored Programs”.
Older versions of MySQL employed
but this syntax is deprecated in favor of
statement that assigns variable values is not written to the
binary log, so in replication scenarios it affects only the host
on which you execute it. To affect all replication hosts,
execute the statement on each one.
A user variable is written as
@ and is
assigned an expression value as follows:
SET @var_name = expr;
SET @name = 43; SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);
expr can range from simple (a
literal value) to more complex (the value returned by a scalar
applies to parameters and local variables in the context of the
stored object within which they are defined. The following
procedure uses the
counter local variable as
a loop counter:
CREATE PROCEDURE p() BEGIN DECLARE counter INT DEFAULT 0; WHILE counter < 10 DO -- ... do work ... SET counter = counter + 1; END WHILE; END;
Many system variables are dynamic and can be changed at runtime
by using the
statement. For a list, see
Section 22.214.171.124, “Dynamic System Variables”. To change a system
refer to it by name, optionally preceded by a modifier:
To indicate that a variable is a global variable, precede its name by the
GLOBALkeyword or the
SET GLOBAL max_connections = 1000; SET @@global.max_connections = 1000;
SUPERprivilege is required to set global variables.
To indicate that a variable is a session variable, precede its name by the
SESSIONkeyword or either the
SET SESSION sql_mode = 'TRADITIONAL'; SET @@session.sql_mode = 'TRADITIONAL'; SET @@sql_mode = 'TRADITIONAL';
Setting a session variable normally requires no special privilege, although there are exceptions that require the
SUPERprivilege (such as
sql_log_bin). A client can change its own session variables, but not those of any other client.
@@local.are synonyms for
If no modifier is present,
SETchanges the session variable.
An error occurs under these circumstances:
@@global.) when setting a variable that has only a session value:
mysql> SET GLOBAL sql_log_bin = ON; ERROR 1231 (42000): Variable 'sql_log_bin' can't be set to the value of 'ON'
@@global.) when setting a variable that has only a global value:
mysql> SET max_connections = 1000; ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
@@SESSION.) when setting a variable that has only a global value:
mysql> SET SESSION max_connections = 1000; ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
The preceding modifiers apply only to system variables. An error occurs for attempts to apply them to user-defined variables, stored procedure or function parameters, or stored program local variables.
statement can contain multiple variable assignments, separated
by commas. This statement assigns values to a user-defined
variable and a system variable:
SET @x = 1, SESSION sql_mode = '';
If you set multiple system variables, the most recent
in the statement is used for following assignments that have no
Examples of multiple-variable assignment:
SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000; SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000; SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;
If any variable assignment in a
statement fails, the entire statement fails and no variables are
If you change a session system variable, the value remains in effect within your session until you change the variable to a different value or the session ends. The change has no effect on other sessions.
If you change a global system variable, the value is remembered
and used for new sessions until you change the variable to a
different value or the server exits. The change is visible to
any client that accesses the global variable. However, the
change affects the corresponding session variable only for
clients that connect after the change. The global variable
change does not affect the session variable for any current
client sessions (not even the session within which the
GLOBAL statement occurred).
To make a global system variable setting permanent so that it applies across server restarts, you should also set it in an option file.
To set a
GLOBAL value to the compiled-in
MySQL default value or a
SESSION variable to
the current corresponding
GLOBAL value, set
the variable to the value
example, the following two statements are identical in setting
the session value of
max_join_size to the current
SET @@session.max_join_size=DEFAULT; SET @@session.max_join_size=@@global.max_join_size;
Not all system variables can be set to
DEFAULT. In such cases, assigning
DEFAULT results in an error.
It is not permitted to assign
user-defined variables, and not supported for stored procedure
or function parameters or stored program local variables. This
results in an error for user-defined variables, and the results
are undefined for parameters or local variables.
To refer to the value of a system variable in expressions, use
one of the
@@-modifiers. For example, you can
retrieve values in a
statement like this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
For a reference to a system variable in an expression as
@@session.), MySQL returns the session value
if it exists and the global value otherwise. This differs from
SET @@, which always refers
to the session value.
SET ONE_SHOT syntax is
only for internal use for replication:
ONE_SHOT to modify temporarily the values of character
set, collation, and time zone variables to reflect at
rollforward what they were originally.
ONE_SHOT is for internal use only and is
deprecated for MySQL 5.0 and up.
ONE_SHOT is intended for use only with the
permitted set of variables. It changes the variables as
requested, but only for the next
statement. After that, the server resets all character set,
collation, and time zone-related system variables to their
previous values. Example:
mysql> SET ONE_SHOT character_set_connection = latin5; mysql> SET ONE_SHOT collation_connection = latin5_turkish_ci; mysql> SHOW VARIABLES LIKE '%_connection'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_connection | latin5 | | collation_connection | latin5_turkish_ci | +--------------------------+-------------------+ mysql> SHOW VARIABLES LIKE '%_connection'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_connection | latin1 | | collation_connection | latin1_swedish_ci | +--------------------------+-------------------+