SET variable = expr [, variable = expr] ...
variable: {
user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | {PERSIST | @@PERSIST.} system_var_name
  | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}

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”.用户定义的变量在会话中本地创建，并且仅存在于该会话的上下文中；请参阅第9.4节，“用户定义变量”。

A user-defined variable is written as @var_name and is assigned an expression value as follows:用户定义的变量写为@var_name，并分配一个表达式值，如下所示：

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).如这些语句所示，expr的范围可以从简单的（文字值）到更复杂的（标量子查询返回的值）。

The Performance Schema user_variables_by_thread table contains information about user-defined variables. 性能架构user_variables_by_thread表包含有关用户定义变量的信息。See Section 27.12.10, “Performance Schema User-Defined Variable Tables”.请参阅第27.12.10节，“性能架构用户定义变量表”。

SET applies to parameters and local variables in the context of the stored object within which they are defined. SET应用于定义参数和局部变量的存储对象上下文中的参数和局部变量。The following procedure uses the increment procedure parameter and counter local variable:以下过程使用increment过程参数和counter局部变量：

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

The MySQL server maintains system variables that configure its operation. MySQL服务器维护配置其操作的系统变量。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语句更改，以影响当前服务器实例的操作。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.SET还可用于将某些系统变量持久化到数据目录中的mysqld-auto.cnf文件中，以影响后续启动的服务器操作。

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. 要使全局系统变量设置永久化，以便在服务器重新启动时应用，可以将其持久化到数据目录中的mysqld-auto.cnf文件。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. 也可以通过手动修改my.cnf选项文件来进行持久的配置更改，但这样做更麻烦，而且手动输入的设置中的错误可能要过很久才能发现。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. 保留系统变量的SET语句更方便，并且避免了设置格式错误的可能性，因为带有语法错误的设置不会成功，也不会更改服务器配置。For more information about persisting system variables and the mysqld-auto.cnf file, see Section 5.1.9.3, “Persisted System Variables”.有关持久化系统变量和mysqld-auto.cnf文件的更多信息，请参阅第5.1.9.3节，“持久化系统变量”。

The following discussion describes the syntax options for setting and persisting system variables:以下讨论介绍了设置和持久化系统变量的语法选项：

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. 要将全局系统变量值设置为在MySQL中编译的默认值，或将会话系统变量设置为当前对应的全局值，请将该变量设置为值DEFAULT。For example, the following two statements are identical in setting the session value of max_join_size to the current global value:例如，以下两条语句在将max_join_size的会话值设置为当前全局值时是相同的：

SET @@SESSION.max_join_size = DEFAULT;
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. 使用SET将全局系统变量持久化为DEFAULT或其文字默认值将为该变量指定默认值，并将该变量的设置添加到mysqld-auto.cnf。To remove the variable from the file, use RESET PERSIST.若要从文件中删除变量，请使用RESET PERSIST。

Some system variables cannot be persisted or are persist-restricted. 某些系统变量无法持久化或受到持久化限制。See Section 5.1.9.4, “Nonpersistible and Persist-Restricted System Variables”.请参阅第5.1.9.4节，“不可逆和持续受限系统变量”。

A system variable implemented by a plugin can be persisted if the plugin is installed when the SET statement is executed. 如果在执行SET语句时安装了插件，则可以持久化插件实现的系统变量。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 no longer exists when the server reads the mysqld-auto.cnf file. 如果不再安装插件，则当服务器读取mysqld-auto.cnf文件时，插件变量不再存在。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:要显示系统变量名称和值，请执行以下操作：

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语句中的任何变量赋值失败，则整个语句将失败，并且不会更改任何变量，mysqld-auto.cnf文件也不会更改。

SET produces an error under the circumstances described here. 在这里描述的情况下，SET会产生一个错误。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.).大多数示例都显示了使用关键字语法的SET语句（例如，GLOBAL或SESSION），但对于使用相应修饰符的语句（例如，@@GLOBAL.或@@SESSION），原则也是正确的。

A SET statement can contain multiple variable assignments, separated by commas. SET语句可以包含多个变量赋值，用逗号分隔。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 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.如果在一条语句中设置多个系统变量，则语句中最近的GLOBAL、PERSIST、PERSIST_ONLY或SESSION关键字将用于以下未指定关键字的赋值。

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;

The @@GLOBAL., @@PERSIST., @@PERSIST_ONLY., @@SESSION., and @@ modifiers apply only to the immediately following system variable, not any remaining system variables. @@GLOBAL.、@@PERSIST.、@@PERSIST_ONLY.、@@SESSION.和@@修饰符仅适用于紧跟其后的系统变量，而不适用于任何剩余的系统变量。This statement sets the sort_buffer_size global value to 50000 and the session value to 1000000:此语句将sort_buffer_size全局值设置为50000，将会话值设置为1000000：

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

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). 若要在表达式中引用系统变量的值，请使用其中一个@@-修饰符（除了@@PERSIST.和@@PERSIST_ONLY.，这在表达式中是不允许的）。For example, you can retrieve system variable values in a SELECT statement like this:例如，您可以在SELECT语句中检索系统变量值，如下所示：

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