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.3, “Server System Variables”,
Section 5.1.4, “Session 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
and SET
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.4, “SET PASSWORD
Syntax”.
SET
TRANSACTION ISOLATION LEVEL
sets the isolation level
for transaction processing. See
Section 12.3.6, “SET TRANSACTION
Syntax”.
The following discussion shows the different
SET
syntaxes
that you can use to set variables. The examples use the
=
assignment operator, but the
:=
operator also is allowable.
A user variable is written as
@
and can be
set as follows:
var_name
SET @var_name
=expr
;
As of MySQL 4.0.3, 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.
. The
SUPER
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 for SESSION
and
@@session.
.
If no modifier is present,
SET
changes the session variable.
MySQL Enterprise. The MySQL Enterprise Monitor makes extensive use of system variables to determine the state of your server. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
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.25, “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. Before MySQL 4.1, the only allowable value for
charset_name
is
cp1251_koi8
, but you can add new mappings
by editing the sql/convert.cc
file in the
MySQL source distribution. As of MySQL 4.1.1, SET
CHARACTER SET
sets three session system variables:
character_set_client
and
character_set_results
are set
to the given character set, and
character_set_connection
to
the value of
character_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
cannot be used as a client character
set, which means that it does not work for SET
CHARACTER SET
.
NAMES {'
charset_name
'
[COLLATE 'collation_name
'] |
DEFAULT}
SET NAMES
sets the three session system
variables
character_set_client
,
character_set_connection
, and
character_set_results
to the
given character set. Setting
character_set_connection
to
charset_name
also sets
collation_connection
to the
default collation for charset_name
. The
optional COLLATE
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
cannot be used as a client character
set, which means that it does not work for SET
NAMES
.
SET NAMES
is available as of MySQL 4.1.0.
This option is a modifier, not a variable. It can be used to
influence the effect of variables that set the character set,
the collation, and the time zone. ONE_SHOT
is primarily used for replication purposes:
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 available as of MySQL 4.1.3.
You cannot use ONE_SHOT
with other than the
allowed set of variables; if you try, you get an error like
this:
mysql> SET ONE_SHOT max_allowed_packet = 1;
ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes
internal to the MySQL server
If ONE_SHOT
is used with the allowed
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 | +--------------------------+-------------------+
User Comments
"Programmer Beware"... the syntax SET @var_name = expr; can lead to spurious InnoDB locks where the equivalent SELECT expr INTO @var_name; does not.
1 row in set (0.00 sec)Simple example:
SESSION #1
==========
mysql> create table t1(a int primary key) engine=innodb;
Query OK, 0 rows affected (0.20 sec)
mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (4);
Query OK, 1 row affected (0.01 sec)
mysql> update t1 set a=5 where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SESSION #2 (do while the above is still open)
==========
mysql> set @test = (select max(a) from t1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select max(a) from t1 into @test;
Query OK, 1 row affected (0.00 sec)
mysql> select @test;
Add your own comment.