SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | param_name = expr
    | local_var_name = expr
    | [GLOBAL | SESSION]
        system_var_name = expr
    | [@@global. | @@session. | @@]
        system_var_name = expr

SET ONE_SHOT system_var_name = expr

SET syntax for variable assignment enables you to assign values to different types of variables that affect the operation of the server or clients:

Older versions of MySQL employed SET OPTION, but this syntax is deprecated in favor of SET without OPTION.

A SET statement that assigns variable values is not written to the binary log, so in replication scenarios it affects only the host on which you execute it. To affect all replication hosts, execute the statement on each one.

The following examples illustrate SET syntax for setting variables. They use the = assignment operator, but the := assignment operator is also permitted for this purpose.

A user variable is written as @var_name and is assigned an expression value as follows:

SET @var_name = expr;

Examples:

SET @name = 43;
SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);

The expr can range from simple (a literal value) to more complex (the value returned by a scalar subquery).

SET applies to parameters and local variables in the context of the stored object within which they are defined. The following procedure uses the counter local variable as a loop counter:

CREATE PROCEDURE p()
BEGIN
  DECLARE counter INT DEFAULT 0;
  WHILE counter < 10 DO
    -- ... do work ...
    SET counter = counter + 1;
  END WHILE;
END;

Many system variables are dynamic and can be changed at runtime by using the SET statement. For a list, see Section 5.1.6.2, “Dynamic System Variables”. To change a system variable with SET, refer to it by name, optionally preceded by a modifier:

The preceding modifiers apply only to system variables. An error occurs for attempts to apply them to user-defined variables, stored procedure or function parameters, or stored program local variables.

A SET statement can contain multiple variable assignments, separated by commas. This statement assigns values to a user-defined variable and a system variable:

SET @x = 1, SESSION sql_mode = '';

If you set multiple system variables, the most recent GLOBAL or SESSION modifier in the statement is used for following assignments that have no modifier specified.

Examples of multiple-variable assignment:

SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

If any variable assignment in a SET statement fails, the entire statement fails and no variables are changed.

If you change a session system variable, the value remains in effect within your session until you change the variable to a different value or the session ends. The change has no effect on other sessions.

If you change a global system variable, the value is remembered and used for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the 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 current client sessions (not even the session within which the SET GLOBAL statement occurred).

To make a global system variable setting permanent so that it applies across server restarts, you should also set it in an option file.

To set a GLOBAL value to the compiled-in MySQL default value or a SESSION variable to the current corresponding GLOBAL value, set the variable to the value DEFAULT. For example, the following two statements are identical in setting the session value of max_join_size to the current global value:

SET @@session.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, assigning DEFAULT results in an error.

It is not permitted to assign DEFAULT to user-defined variables, and not supported for stored procedure or function parameters or stored program local variables. This results in an error for user-defined variables, and the results are undefined for parameters or local variables.

To refer to the value of a system variable in expressions, use 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;

For a reference to a system variable in an expression as @@var_name (rather than with @@global. or @@session.), MySQL returns the session value if it exists and the global value otherwise. This differs from SET @@var_name = expr, which always refers to the session value.

The SET ONE_SHOT syntax 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 |
+--------------------------+-------------------+