Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 32.3Mb
PDF (A4) - 32.3Mb
PDF (RPM) - 30.4Mb
HTML Download (TGZ) - 7.8Mb
HTML Download (Zip) - 7.8Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 142.5Kb
Man Pages (Zip) - 201.7Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


MySQL 8.0 Reference Manual  /  ...  /  SET Syntax for Variable Assignment

Pre-General Availability Draft: 2017-05-29

14.7.4.1 SET Syntax for Variable Assignment

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | param_name = expr
    | local_var_name = expr
    | [GLOBAL | SESSION | PERSIST]
        system_var_name = expr
    | [@@global. | @@session. | @@persist. | @@]
        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:

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

As demonstrated by those statements, 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 6.1.6.2, “Dynamic System Variables”. To change a system variable with SET, refer to it by name, optionally preceded by a modifier:

  • To indicate that a variable is a global variable, precede its name by the GLOBAL keyword or the @@global. qualifier:

    SET GLOBAL max_connections = 1000;
    SET @@global.max_connections = 1000;
    

    The SYSTEM_VARIABLES_ADMIN or SUPER privilege is required to set global variables.

  • Another way to set a global variable is to precede its name by the PERSIST keyword or the @@persist. qualifier:

    SET PERSIST max_connections = 1000;
    SET @@persist.max_connections = 1000;
    

    This SET syntax enables you to make configuration changes at runtime that also persist across server restarts. Like SET GLOBAL, SET PERSIST changes the runtime variable value, but also writes the variable setting to an option file named mysqld-auto.cnf in the data directory (replacing any existing variable setting if there is one). At startup, the server processes this file after all other option files. The SYSTEM_VARIABLES_ADMIN or SUPER privilege is required to persist global variables.

    Note

    Management of the mysqld-auto.cnf file should be left to the server and not performed manually:

    • Removal of the file results in a loss of all persisted settings at the next server startup. (This is permissible if your intent is to reconfigure the server without these settings.) To remove all settings in the file without removing the file itself, use this statement:

      RESET PERSIST;
      
    • Manual changes to the file may result in a parse error at server startup. In this case, the server reports an error and exits. If this issue occurs, start the server with the persisted_globals_load system variable disabled or with the --no-defaults option. Alternatively, remove the mysqld-auto.cnf file, but, as noted previously, removing this file results in a loss of all persisted settings.

    A plugin variable can be persisted if the plugin is installed when SET PERSIST is executed. Assignment of the persisted plugin variable takes effect for subsequent server restarts if the plugin is still installed. If the plugin is no longer installed, the plugin variable will not exist when the server reads the mysqld-auto.cnf file. In this case, the server writes a warning to the error log and continues:

    currently unknown variable 'var_name'
    was read from the persisted config file
    
  • To indicate that a variable is a session variable, precede its name by the SESSION keyword or either the @@session. or @@ qualifier:

    SET SESSION sql_mode = 'TRADITIONAL';
    SET @@session.sql_mode = 'TRADITIONAL';
    SET @@sql_mode = 'TRADITIONAL';
    

    Setting a session variable normally requires no special privilege, although there are exceptions that require the SYSTEM_VARIABLES_ADMIN or SUPER privilege (such as sql_log_bin). A client can change its own session variables, but not those of any other client.

    Session-only system variables cannot be persisted. They cannot be set at server startup, so there is no reason to list them in mysqld-auto.cnf.

  • LOCAL and @@local. are synonyms for SESSION and @@session..

  • If no modifier is present, SET changes the session variable.

  • An error occurs under these circumstances:

    • Use of SET GLOBAL (or @@global.) or SET PERSIST (or @@persist.) when setting a variable that has only a session value:

      mysql> SET GLOBAL sql_log_bin = ON;
      ERROR 1231 (42000): Variable 'sql_log_bin' can't be
      set to the value of 'ON'
      
    • Omission of GLOBAL (or @@global.) or PERSIST (or @@persist.) when setting a variable that has only a global value:

      mysql> SET max_connections = 1000;
      ERROR 1229 (HY000): Variable 'max_connections' is a
      GLOBAL variable and should be set with SET GLOBAL
      
    • Use of SET SESSION (or @@SESSION.) when setting a variable that has only a global value:

      mysql> SET SESSION max_connections = 1000;
      ERROR 1229 (HY000): Variable 'max_connections' is a
      GLOBAL variable and should be set with SET GLOBAL
      

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, PERSIST, 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, nor is the mysqld-auto.cnf file 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, modify it with SET PERSIST to record it in the mysqld-auto.cnf file. It is also possible to use SET GLOBAL and manually modify a my.cnf file, but that is more cumbersome, and an error in a manually entered setting might not be discovered until much later. SET PERSIST is more convenient and avoids the possibility of malformed settings.

The Performance Schema persisted_variables table provides an SQL interface to the mysqld-auto.cnf file, enabling its contents to be inspected at runtime using SELECT statements. See Section 24.11.13.1, “Performance Schema persisted_variables Table”.

The Performance Schema variables_info table contains information showing when and by which user each system variable was most recently set. See Section 24.11.13.2, “Performance Schema variables_info Table”.

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.

With SET PERSIST (or @@persist.), setting a global variable to DEFAULT or to its literal default value assigns the variable its default value and adds a setting for the variable to mysqld-auto.cnf. To remove the variable from the file, use RESET PERSIST.

An error occurs for attempts to assign DEFAULT to user-defined variables, stored procedure or function parameters, or stored program local variables.

To refer to the value of a system variable in expressions, use one of the @@-modifiers (except @@persist., which is not permitted in expressions). 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.


User Comments
Sign Up Login You must be logged in to post a comment.