SETvariable_assignment
[,variable_assignment
] ...variable_assignment
:user_var_name
=expr
| [GLOBAL | SESSION]system_var_name
=expr
| [@@global. | @@session. | @@]system_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
is deprecated in favor of
SET
without
OPTION
.
This section describes use of
SET
for
assigning values to system variables or user variables. For
general information about these types of variables, see
Section 5.1.4, “Server System Variables”, and
Section 8.4, “User-Defined Variables”. System variables also can be set
at server startup, as described in
Section 5.1.5, “Using System Variables”.
Some variants of
SET
syntax
are used in other contexts:
SET CHARACTER SET
andSET NAMES
assign values to character set and collation variables associated with the connection to the server.SET ONESHOT
is used for replication. These variants are described later in this section.SET PASSWORD
assigns account passwords. See Section 12.4.1.6, “SET PASSWORD
Syntax”.SET TRANSACTION ISOLATION LEVEL
sets the isolation level for transaction processing. See Section 12.3.6, “SET TRANSACTION
Syntax”.SET
is used within stored routines to assign values to local routine variables. See Section 12.7.3.2, “VariableSET
Statement”.
The following discussion shows the different
SET
syntaxes
that you can use to set variables. The examples use the
=
assignment
operator, but you can also use the
:=
assignment operator for this purpose.
A user variable is written as
@
and can be
set as follows:
var_name
SET @var_name
=expr
;
Many system variables are dynamic and can be changed while the
server runs by using the
SET
statement. For a list, see
Section 5.1.5.2, “Dynamic System Variables”. To change a system
variable with
SET
, refer
to it as var_name
, optionally preceded
by a modifier:
To indicate explicitly that a variable is a global variable, precede its name by
GLOBAL
or@@global.
. TheSUPER
privilege is required to set global variables.To indicate explicitly that a variable is a session variable, precede its name by
SESSION
,@@session.
, or@@
. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.LOCAL
and@@local.
are synonyms forSESSION
and@@session.
.If no modifier is present,
SET
changes the session variable.
A SET
statement can contain multiple variable assignments, separated by
commas. If you set several system variables, the most recent
GLOBAL
or SESSION
modifier
in the statement is used for following variables that have no
modifier specified.
Examples:
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
The @@
syntax for system variables is supported for compatibility with
some other database systems.
var_name
If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.
If you change a global system variable, the value is remembered
and used for new connections until the server restarts. (To make a
global system variable setting permanent, you should set it in an
option file.) The change is visible to any client that accesses
that 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 client that is currently connected (not even that
of the client that issues the
SET GLOBAL
statement).
To prevent incorrect usage, MySQL produces an error if you use
SET GLOBAL
with a variable that can only be used with
SET SESSION
or if you do not specify GLOBAL
(or
@@global.
) when setting a global variable.
To set a SESSION
variable to the
GLOBAL
value or a GLOBAL
value to the compiled-in MySQL default value, use the
DEFAULT
keyword. For example, the following two
statements are identical in setting the session value of
max_join_size
to the global
value:
SET 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, use of DEFAULT
results in an
error.
You can refer to the values of specific global or sesson system
variables in expressions by using one of the
@@
-modifiers. For example, you can retrieve
values in a SELECT
statement like
this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
When you refer to a system variable in an expression as
@@
(that is,
when you do not specify var_name
@@global.
or
@@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.)
var_name
=
value
Some variables displayed by SHOW VARIABLES
may not be available using SELECT
@@
syntax; an
var_name
Unknown system variable
occurs. As a
workaround in such cases, you can use SHOW VARIABLES
LIKE '
.
var_name
'
Suffixes for specifying a value multiplier can be used when
setting a variable at server startup, but not to set the value
with SET
at
runtime. On the other hand, with
SET
you can
assign a variable's value using an expression, which is not true
when you set a variable at server startup. For example, the first
of the following lines is legal at server startup, but the second
is not:
shell>mysql --max_allowed_packet=16M
shell>mysql --max_allowed_packet=16*1024*1024
Conversely, the second of the following lines is legal at runtime, but the first is not:
mysql>SET GLOBAL max_allowed_packet=16M;
mysql>SET GLOBAL max_allowed_packet=16*1024*1024;
To display system variables names and values, use the
SHOW VARIABLES
statement. (See
Section 12.4.5.40, “SHOW VARIABLES
Syntax”.)
The following list describes
SET
options
that have nonstandard syntax (that is, options that are not set
with
syntax).
name
=
value
CHARACTER SET {
charset_name
| DEFAULT}This maps all strings from and to the client with the given mapping. You can add new mappings by editing
sql/convert.cc
in the MySQL source distribution.SET CHARACTER SET
sets three session system variables:character_set_client
andcharacter_set_results
are set to the given character set, andcharacter_set_connection
to the value ofcharacter_set_database
. See Section 9.1.4, “Connection Character Sets and Collations”.The default mapping can be restored by using the value
DEFAULT
. The default depends on the server configuration.ucs2
,utf16
, andutf32
cannot be used as a client character set, which means that they do not work forSET CHARACTER SET
.NAMES {'
charset_name
' [COLLATE 'collation_name
'] | DEFAULT}SET NAMES
sets the three session system variablescharacter_set_client
,character_set_connection
, andcharacter_set_results
to the given character set. Settingcharacter_set_connection
tocharset_name
also setscollation_connection
to the default collation forcharset_name
. The optionalCOLLATE
clause may be used to specify a collation explicitly. See Section 9.1.4, “Connection Character Sets and Collations”.The default mapping can be restored by using a value of
DEFAULT
. The default depends on the server configuration.ucs2
,utf16
, andutf32
cannot be used as a client character set, which means that they do not work forSET NAMES
.This option is a modifier, not a variable. It is only for internal use for replication: mysqlbinlog uses
SET 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 non-SET
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 | +--------------------------+-------------------+