Documentation Home
MySQL 8.0 参考手册  / 第 13 章 SQL 语句  / 13.7 数据库管理语句  / 13.7.6 SET 语句  /  13.7.6.1 变量赋值的 SET 语法

13.7.6.1 变量赋值的 SET 语法

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
}

SET 变量赋值的语法使您能够为影响服务器或客户端操作的不同类型的变量赋值:

分配变量值的SET 语句不会写入二进制日志,因此在复制场景中它只会影响您执行它的主机。要影响所有复制主机,请在每个主机上执行该语句。

以下部分描述 SET 了设置变量的语法。他们使用 = 赋值运算符,但 := 为此目的也允许使用赋值运算符。

用户自定义变量赋值

用户定义的变量是在会话中本地创建的,并且仅存在于该会话的上下文中;参见 第 9.4 节,“用户定义的变量”

用户定义的变量被写为 并被分配一个表达式值,如下所示: @var_name

SET @var_name = expr;

例子:

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

如这些语句所示, expr范围可以从简单(文字值)到更复杂(标量子查询返回的值)。

Performance Schema user_variables_by_thread表包含有关用户定义变量的信息。请参阅 第 27.12.10 节,“性能模式用户定义的变量表”

参数和局部变量赋值

SET 适用于定义它们的存储对象的上下文中的参数和局部变量。以下过程使用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;
系统变量赋值

MySQL 服务器维护配置其操作的系统变量。一个系统变量可以有一个影响整个服务器操作的全局值,一个影响当前会话的会话值,或者两者兼而有之。许多系统变量是动态的,可以在运行时使用 SET 语句更改以影响当前服务器实例的操作。 SET 也可以用来将某些系统变量持久化到 mysqld-auto.cnf数据目录下的文件中,影响后续启动的服务器运行。

如果SET针对敏感系统变量发出语句,则 在将其记录到通用日志和审计日志之前,将重写查询以用<redacted>”替换该值。即使通过密钥环组件的安全存储在服务器实例上不可用,也会发生这种情况。

如果您更改会话系统变量,该值将在您的会话中保持有效,直到您将该变量更改为不同的值或会话结束。更改对其他会话没有影响。

如果您更改全局系统变量,该值将被记住并用于初始化新会话的会话值,直到您将该变量更改为不同的值或服务器退出。任何访问全局值的客户端都可以看到更改。但是,更改只会影响更改后连接的客户端的相应会话值。全局变量更改不会影响任何当前客户端会话的会话值(甚至不会影响发生全局值更改的会话)。

要使全局系统变量设置永久化以便它在服务器重新启动时应用,您可以将其保存到 mysqld-auto.cnf数据目录中的文件中。也可以通过手动修改 my.cnf选项文件来进行持久的配置更改,但这比较麻烦,而且手动输入的设置中的错误可能要到很久以后才会被发现。 SET 保留系统变量的语句更方便,并且避免了格式错误设置的可能性,因为语法错误的设置不会成功并且不会更改服务器配置。有关持久化系统变量和mysqld-auto.cnf 文件的更多信息,请参阅第 5.1.9.3 节,“持久化系统变量”

笔记

设置或保留全局系统变量值始终需要特殊权限。设置会话系统变量值通常不需要特殊权限,并且可以由任何用户完成,但也有例外。有关详细信息,请参阅 第 5.1.9.1 节,“系统变量权限”

以下讨论描述了用于设置和持久化系统变量的语法选项:

  • 要为全局系统变量赋值,请在变量名称前加上GLOBAL关键字或@@GLOBAL.限定符:

    SET GLOBAL max_connections = 1000;
    SET @@GLOBAL.max_connections = 1000;
  • 要为会话系统变量赋值,请在变量名称前加上SESSIONor LOCAL关键字、 @@SESSION.@@LOCAL.@@ 限定符,或者不使用关键字或根本不使用任何修饰符:

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

    客户端可以更改自己的会话变量,但不能更改任何其他客户端的会话变量。

  • 要将全局系统变量持久保存到 mysqld-auto.cnf数据目录中的选项文件,请在变量名称前加上 PERSIST关键字或 @@PERSIST.限定符:

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

    SET 语法使您能够在运行时进行配置更改,这些更改也会在服务器重新启动时保持不变。与 一样 SET GLOBALSET PERSIST设置全局变量运行时值,但也将变量设置写入 mysqld-auto.cnf文件(如果有,则替换任何现有的变量设置)。

  • 要在不设置全局变量运行时值的情况下将全局系统变量持久保存到 mysqld-auto.cnf文件中,请在变量名前面加上PERSIST_ONLY关键字或 @@PERSIST_ONLY.限定符:

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

    PERSISTPERSIST_ONLY将变量设置写入mysqld-auto.cnf。但是,与 不同的是PERSISTPERSIST_ONLY不会修改全局变量运行时的值。这 PERSIST_ONLY适用于配置只能在服务器启动时设置的只读系统变量。

要将全局系统变量值设置为编译的 MySQL 默认值或将会话系统变量设置为当前对应的全局值,请将变量设置为 value DEFAULT。例如,以下两个语句在将会话值设置 max_join_size为当前全局值时是相同的:

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

使用 SET将全局系统变量持久化 DEFAULT为其文字默认值或其文字默认值会为该变量分配其默认值并将该变量的设置添加到mysqld-auto.cnf。要从文件中删除变量,请使用 RESET PERSIST.

某些系统变量无法持久化或受到持久化限制。请参阅 第 5.1.9.4 节,“不可持久和持久受限的系统变量”

SET 如果在执行语句 时安装了插件,则插件实现的系统变量可以持久化 。如果仍然安装了插件,则持久化插件变量的分配会在随后的服务器重新启动时生效。mysqld-auto.cnf如果不再安装插件,则服务器读取文件时插件变量不再存在。在这种情况下,服务器向错误日志写入警告并继续:

currently unknown variable 'var_name'
was read from the persisted config file

显示系统变量名称和值:

设置错误处理

如果语句中的任何变量赋值 SET 失败,则整个语句都会失败,并且不会更改任何变量,也不会更改mysqld-auto.cnf 文件。

SET 在此处描述的情况下会产生错误。大多数示例显示 SET 使用关键字语法(例如, GLOBALSESSION)的语句,但这些原则也适用于使用相应修饰符(例如, @@GLOBAL.@@SESSION.)的语句。

  • 使用 SET (任何变体)设置只读变量:

    mysql> SET GLOBAL version = 'abc';
    ERROR 1238 (HY000): Variable 'version' is a read only variable
  • 使用GLOBALPERSISTPERSIST_ONLY来设置只有一个会话值的变量:

    mysql> SET GLOBAL sql_log_bin = ON;
    ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION
    variable and can't be used with SET GLOBAL
  • 使用 ofSESSION设置只有全局值的变量:

    mysql> SET SESSION max_connections = 1000;
    ERROR 1229 (HY000): Variable 'max_connections' is a
    GLOBAL variable and should be set with SET GLOBAL
  • 省略GLOBAL, PERSIST, or PERSIST_ONLY来设置一个只有全局值的变量:

    mysql> SET max_connections = 1000;
    ERROR 1229 (HY000): Variable 'max_connections' is a
    GLOBAL variable and should be set with SET GLOBAL
  • 使用PERSISTor PERSIST_ONLY设置无法持久化的变量:

    mysql> SET PERSIST port = 3307;
    ERROR 1238 (HY000): Variable 'port' is a read only variable
    mysql> SET PERSIST_ONLY port = 3307;
    ERROR 1238 (HY000): Variable 'port' is a non persistent read only variable
  • @@GLOBAL.@@PERSIST.@@PERSIST_ONLY.和 修饰符仅适用于系统变量@@SESSION.@@尝试将它们应用于用户定义的变量、存储过程或函数参数或存储的程序局部变量时会发生错误。

  • 并非所有系统变量都可以设置为 DEFAULT。在这种情况下,分配 DEFAULT会导致错误。

  • 尝试分配 DEFAULT给用户定义的变量、存储过程或函数参数或存储的程序局部变量时会发生错误。

多变量赋值

一条SET 语句可以包含多个变量赋值,以逗号分隔。此语句将值分配给用户定义的变量和系统变量:

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

如果您在单个语句中设置多个系统变量,则语句中最近的GLOBAL, PERSIST, PERSIST_ONLY, 或SESSION关键字用于后续未指定关键字的赋值。

多变量赋值的例子:

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;

@@GLOBAL.@@PERSIST.、 和 修饰符仅适用于紧随其后的系统变量,不适用于任何剩余的系统变量@@PERSIST_ONLY.。 此语句将 全局值设置为 50000,将会话值设置为 1000000: @@SESSION.@@sort_buffer_size

SET @@GLOBAL.sort_buffer_size = 50000, sort_buffer_size = 1000000;
表达式中的系统变量引用

要在表达式中引用系统变量的值,请使用 - 修饰符之一(表达式中不允许使用的和 @@除外 )。例如,您可以在如下语句中检索系统变量值: @@PERSIST.@@PERSIST_ONLY.SELECT

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

表达式中对系统变量的引用 as (with而不是 or )如果存在则返回会话值,否则返回全局值。这不同于 ,后者始终引用会话值。 @@var_name@@@@GLOBAL.@@SESSION.SET @@var_name = expr