Rechercher dans le manuel MySQL

13.7.5.1 SET Syntax for Variable Assignment

Contents
  1. SET variable = expr [, variable = expr] ...
  2.  
  3. variable: {
  4.     user_var_name
  5.   | param_name
  6.   | local_var_name
  7.   | {GLOBAL | @@GLOBAL.} system_var_name
  8.   | {PERSIST | @@PERSIST.} system_var_name
  9.   | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
  10.   | [SESSION | @@SESSION. | @@] system_var_name
  11. }

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

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

User-Defined Variable Assignment

User-defined variables are created locally within a session and exist only within the context of that session; see Section 9.4, “User-Defined Variables”.

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

  1. SET @var_name = expr;

Examples:

  1. SET @name = 43;
  2. 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).

The Performance Schema user_variables_by_thread table contains information about user-defined variables. See Section 26.12.10, “Performance Schema User-Defined Variable Tables”.

Contents Haut

Parameter and Local Variable Assignment

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

  1. CREATE PROCEDURE p(increment INT)
  2. BEGIN
  3.   DECLARE counter INT DEFAULT 0;
  4.   WHILE counter < 10 DO
  5.     -- ... do work ...
  6.     SET counter = counter + increment;
  7.   END WHILE;
  8. END;

Contents Haut

System Variable Assignment

The MySQL server maintains system variables that configure its operation. A system variable can have a global value that affects server operation as a whole, a session value that affects the current session, or both. Many system variables are dynamic and can be changed at runtime using the SET statement to affect operation of the current server instance. SET can also be used to persist certain system variables to the mysqld-auto.cnf file in the data directory, to affect server operation for subsequent startups.

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 to initialize the session value 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 value. However, the change affects the corresponding session value only for clients that connect after the change. The global variable change does not affect the session value for any current client sessions (not even the session within which the global value change occurs).

To make a global system variable setting permanent so that it applies across server restarts, you can persist it to the mysqld-auto.cnf file in the data directory. It is also possible to make persistent configuration changes by manually modifying a my.cnf option file, but that is more cumbersome, and an error in a manually entered setting might not be discovered until much later. SET statements that persist system variables are more convenient and avoid the possibility of malformed settings because settings with syntax errors do not succeed and do not change server configuration. For more information about persisting system variables and the mysqld-auto.cnf file, see Section 5.1.9.3, “Persisted System Variables”.

Note

Setting or persisting a global system variable value always requires special privileges. Setting a session system variable value normally requires no special privileges and can be done by any user, although there are exceptions. For more information, see Section 5.1.9.1, “System Variable Privileges”.

The following discussion describes the syntax options for setting and persisting system variables:

  • To assign a value to a global system variable, precede the variable name by the GLOBAL keyword or the @@GLOBAL. qualifier:

    1. SET GLOBAL max_connections = 1000;
    2. SET @@GLOBAL.max_connections = 1000;

  • To assign a value to a session system variable, precede the variable name by the SESSION or LOCAL keyword, by the @@SESSION., @@LOCAL., or @@ qualifier, or by no keyword or no modifier at all:

    1. SET SESSION sql_mode = 'TRADITIONAL';
    2. SET LOCAL sql_mode = 'TRADITIONAL';
    3. SET @@SESSION.sql_mode = 'TRADITIONAL';
    4. SET @@LOCAL.sql_mode = 'TRADITIONAL';
    5. SET @@sql_mode = 'TRADITIONAL';
    6. SET sql_mode = 'TRADITIONAL';

    A client can change its own session variables, but not those of any other client.

  • To persist a global system variable to the mysqld-auto.cnf option file in the data directory, precede the variable name by the PERSIST keyword or the @@PERSIST. qualifier:

    1. SET PERSIST max_connections = 1000;
    2. 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 sets the global variable runtime value, but also writes the variable setting to the mysqld-auto.cnf file (replacing any existing variable setting if there is one).

  • To persist a global system variable to the mysqld-auto.cnf file without setting the global variable runtime value, precede the variable name by the PERSIST_ONLY keyword or the @@PERSIST_ONLY. qualifier:

    1. SET PERSIST_ONLY back_log = 100;
    2. SET @@PERSIST_ONLY.back_log = 100;

    Like PERSIST, PERSIST_ONLY writes the variable setting to mysqld-auto.cnf. However, unlike PERSIST, PERSIST_ONLY does not modify the global variable runtime value. This makes PERSIST_ONLY suitable for configuring read-only system variables that can be set only at server startup.

To set a global system variable value to the compiled-in MySQL default value or a session system 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:

  1. SET @@SESSION.max_join_size = DEFAULT;
  2. SET @@SESSION.max_join_size = @@GLOBAL.max_join_size;

Using SET to persist a global system variable to a value of 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.

Some system variables cannot be persisted or are persist-restricted. See Section 5.1.9.4, “Nonpersistible and Persist-Restricted System Variables”.

A system variable implemented by a plugin can be persisted if the plugin is installed when the SET statement 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 display system variable names and values:

Contents Haut

SET Error Handling

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.

SET produces an error under the circumstances described here. Most of the examples show SET statements that use keyword syntax (for example, GLOBAL or SESSION), but the principles are also true for statements that use the corresponding modifiers (for example, @@GLOBAL. or @@SESSION.).

  • Use of SET (any variant) to set a read-only variable:

    1. mysql> SET GLOBAL version = 'abc';
    2. ERROR 1238 (HY000): Variable 'version' is a read only variable

  • Use of GLOBAL, PERSIST, or PERSIST_ONLY to set a variable that has only a session value:

    1. mysql> SET GLOBAL sql_log_bin = ON;
    2. ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION
    3. variable and can't be used with SET GLOBAL

  • Use of SESSION to set a variable that has only a global value:

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

  • Omission of GLOBAL, PERSIST, or PERSIST_ONLY to set a variable that has only a global value:

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

  • Use of PERSIST or PERSIST_ONLY to set a variable that cannot be persisted:

    1. mysql> SET PERSIST port = 3307;
    2. ERROR 1238 (HY000): Variable 'port' is a read only variable
    3. mysql> SET PERSIST_ONLY port = 3307;
    4. ERROR 1238 (HY000): Variable 'port' is a non persistent read only variable

  • The @@GLOBAL., @@PERSIST., @@PERSIST_ONLY., @@SESSION., and @@ 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.

  • Not all system variables can be set to DEFAULT. In such cases, assigning DEFAULT results in an error.

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

Contents Haut

Multiple Variable Assignment

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

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

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

Examples of multiple-variable assignment:

  1. SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
  2. SET @@GLOBAL.sort_buffer_size = 1000000, @@LOCAL.sort_buffer_size = 1000000;
  3. SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

The @@GLOBAL., @@PERSIST., @@PERSIST_ONLY., @@SESSION., and @@ modifiers apply only to the immediately following system variable, not any remaining system variables. This statement sets the sort_buffer_size global value to 50000 and the session value to 1000000:

  1. SET @@GLOBAL.sort_buffer_size = 50000, sort_buffer_size = 1000000;

Contents Haut

System Variable References in Expressions

To refer to the value of a system variable in expressions, use one of the @@-modifiers (except @@PERSIST. and @@PERSIST_ONLY., which are not permitted in expressions). For example, you can retrieve system variable values in a SELECT statement like this:

  1. SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@sql_mode;
Note

A reference to a system variable in an expression as @@var_name (with @@ rather than @@GLOBAL. or @@SESSION.) 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.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-set-variable.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut