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.

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.5.2, "Dynamic System Variables". To change a system variable with SET, refer to it as var_name, optionally preceded by a modifier:

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.

It is not permitted to assign the value DEFAULT to user-defined variables, stored procedure or function parameters, or stored program local variables. This results in a syntax error for user-defined variables, parameters, and local variables.

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

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=16Mshell> 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 13.7.5.38, "SHOW VARIABLES Syntax".)

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