• MySQL 5.1 Reference Manual :: 12 SQL Statement Syntax :: 12.4 Database Administration Statements :: 12.4.4 SET Syntax

  • 12.4.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 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:

    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 @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.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 @@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 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 @@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.4.5.41, “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 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.

    • 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 allowed set of variables. With other variables, an error occurs: 

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