Skip navigation links
12.7.5 SHOW Syntax »
Section Navigation      [Toggle]

12.7.4. SET Syntax

SET variable_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 variables. The SET statement can be used to assign values to these types of variables:

Some variants of SET syntax are used in other contexts:

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 @var_name and can be set as follows: 

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.4.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.

A SET statement can contain multiple variable assignments, separated by commas. For example, the statement can assign values to a user-defined variable and a system variable. 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 @@var_name syntax for system variables is supported for compatibility with some other database systems.

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 session 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 @@var_name (that is, when you do not specify @@global. or @@session.), MySQL returns the session value if it exists and the global value otherwise. (This differs from SET @@var_name = value, which always refers to the session value.)

Note

Some variables displayed by SHOW VARIABLES may not be available using SELECT @@var_name syntax; an 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.7.5.40, “SHOW VARIABLES Syntax”.)

The following list describes SET options that have nonstandard syntax (that is, options that are not set with name = value syntax).

  • 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 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, utf16, and utf32 cannot be used as a client character set, which means that they do 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, utf16, and utf32 cannot be used as a client character set, which means that they do not work for SET NAMES.

  • ONE_SHOT

    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 |
+--------------------------+-------------------+
Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by A Ramos on October 26 2009 2:34am[Delete] [Edit]

"Programmer Beware"... the syntax SET @var_name = expr; can lead to spurious InnoDB locks where the equivalent SELECT expr INTO @var_name; does not.

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;

+-------+
| @test |
+-------+
| 3     | 
+-------+
1 row in set (0.00 sec)

Posted by thomas seillan on July 6 2010 7:51pm[Delete] [Edit]

I had a very strange problem i couldn't understand with a 'Command out of sync error' using a multi query that would start with a 'select @var:=' statement, and couldn't figure out any solution.

With a lot of testing, and a few Jedi tricks, i figured out that the "select @var:=" syntax was the core of my issue, and that using a simple "set @var:=" syntax would do the trick.

Reason is simple, but i did not know it: "select @var:=" syntax returns a result set (logically^^), whereas "set @var:=" syntax... just sets a variable, wich is what i intended to do. A noob mistake i know, however i hope my post will help.

Add your own comment.