MySQL的SQL语句 - 数据库管理语句 - SET 语句 - 为变量赋值的 SET 语法

林员外聊编程 2021-03-17

2498

SET 语句

SET 语句有几种形式。后面将分别介绍那些与特定服务器功能没有关联的形式:

● SET var_name = value 使您能够为影响服务器或客户端操作的变量赋值。

● SET CHARACTER SET 和 SET NAMES 为与服务器的当前连接关联的字符集和排序规则变量赋值。

其他形式与它们帮助实现的功能相关的其他语句组合在一起：

● SET DEFAULT ROLE 和 SET ROLE 设置用户帐户的默认角色和当前角色。

● SET PASSWORD 分配帐户密码。

● SET RESOURCE GROUP 为资源组分配线程。

● SET TRANSACTION ISOLATION LEVEL 设置事务处理的隔离级别。

为变量赋值的 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 语法。它们使用 = 赋值运算符，但也允许使用 := 赋值运算符。

用户定义变量赋值

用户定义的变量在会话中本地创建，并且仅存在于该会话的上下文中。

用户定义的变量被写为 @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 表包含有关用户定义变量的信息。

参数与局部变量赋值

SET 应用于存储对象上下文中定义的参数和局部变量。以下过程使用增量过程参数和计数器局部变量：

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 文件，以影响后续启动的服务器操作。

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

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

要使全局系统变量设置永久化，以便在服务器重新启动时应用，可以将其持久化到数据目录中的 mysqld-auto.cnf 文件。还可以通过手动修改 my.cnf 选项文件，但这更麻烦，而且手动输入设置中的错误可能要过很久才能发现。持久化系统变量的 SET 语句更方便，并且避免了格式错误的设置，因为有语法错误的设置不会成功，也不会更改服务器配置。

注意

设置或持久化全局系统变量值始终需要特别权限。设置会话系统变量值通常不需要特别权限，任何用户都可以进行设置，尽管也有例外。

下面的讨论描述了设置和持久化系统变量的语法选项：

● 若要为全局系统变量赋值，请在变量名称前面加上 GLOBAL 关键字或 @@GLOBAL. 限定符：

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

● 若要为会话系统变量赋值，请在变量名称前面加上 SESSION 或 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 GLOBAL 一样，SET PERSIST 设置全局变量运行时值，但也将变量设置写入 mysqld-auto.cnf 文件（替换任何现有变量设置）。

● 将全局系统变量持久化到 mysqld-auto.cnf 文件，而不设置全局变量运行时值，请在变量名称前面加上 PERSIST_ONLY 关键字或 @@PERSIST_ONLY. 限定符：

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

类似于 PERSIST，PERSIST_ONLY 将变量设置写入 mysqld-auto.cnf. 但是，与 PERSIST 不同，PERSIST_ONLY 并不修改全局变量运行时值。这使得 PERSIST_ONLY 适用于配置只读系统变量，这些变量只能在服务器启动时设置。

要将全局系统变量值设置为 MySQL 中编译的默认值，或将会话系统变量设置为当前对应的全局值，请将该变量设置为 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。

某些系统变量无法持久化或受到持久化限制。

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

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

要显示系统变量名称和值：

● 请使用 SHOW VARIABLES 语句。

● 几个 Performance Schema 表提供系统变量信息。

● Performance Schema variables_info 表包含的信息显示了每个系统变量是什么时候以及由哪个用户最近设置的。

● Performance Schema persisted_variables 表提供一个到 mysqld-auto.cnf 文件的 SQL 接口，使其内容能够在运行时用 SELECT 语句进行检查。

SET 错误处理

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

在这里描述的情况下，SET 产生一个错误。大多数示例都使用关键字语法的 SET 语句（例如，GLOBAL 或 SESSION），但是对于使用相应修饰符的语句（例如，@@GLOBAL. 或 @@SESSION.），这些原则也是正确的。

● 使用 SET (任何变体) 设置只读变量：

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

● 使用 GLOBAL、PERSIST 或 PERSIST_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

● 使用 SESSION 设置只有全局值的变量：

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 或 PERSIST_ONLY 来设置只有全局值的变量：

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

● 使用 PERSIST 或 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.、@@SESSION. 和 @@ 修饰符只应用于紧跟其后的系统变量，而不应用于任何剩余的系统变量。此语句将 sort_buffer_size 全局值设置为 50000，将会话值设置为 1000000：

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

表达式中的系统变量引用

若要在表达式中引用系统变量的值，请使用一个 @@ 修饰符（除了@@PERSIST 和 @@PERSIST_ONLY，这在表达式中是不允许的）。例如，可以在 SELECT 语句中检索系统变量值，如下所示：

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

注意

表达式中对系统变量的引用为 @@var_name（使用 @@ 而不是 @@GLOBAL. 或 @@SESSION.）如果存在则返回 SESSION 值，否则返回全局值。这与 SET @@var_name = expr 不同，后者总是引用会话值。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/set-statement.html

https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据库管理语句 - SET 语句 - 为变量赋值的 SET 语法

评论