Skip to main content

第 17 篇 MySQL 变量

imodouAbout 9 minMySQL

《MySQL 入门教程》第 17 篇 MySQL 变量

变量是一个拥有名字的对象，可以用于存储和共享数据。根据变量的创建方式和作用域可以将其分为以下几种类型：

  • 系统变量（System Variable），由 MySQL 系统定义和维护的变量。系统变量可能包含全局值（全局变量）和会话值（会话变量），全局变量在服务器启动时通过命令行参数或者选项文件进行设置，在服务关闭时销毁；当客户端连接到服务器时，MySQL 会将大部分的全局变量复制一份作为客户端的会话变量，会话变量在连接断开时销毁。
  • 用户变量（User-Defined Variable），由用户自定义的变量，在连接会话期间有效，可以用于在不同 SQL 语句之间传递数据。
  • 局部变量（Local Variable），在存储过程或者函数中定义的变量，通常用于存储中间结果。局部变量的作用域为存储过程/函数内或者某个语句块之内。

本文主要介绍系统变量和用户变量的设置和使用，局部变量将会在后续关于存储过程的文章中进行讨论。

17.1 系统变量

系统变量由 MySQL 进行维护，用于配置服务器的操作和行为。绝大多数系统变量（编译时设置的只读变量除外）可以在服务器启动时通过命令行参数或者选项文件进行设置，大部分的系统变量也可以使用 SET 命令进行动态修改。

17.1.1 查看系统变量

SHOW VARIABLES语句可以用于查看系统变量，具体的语法如下：

SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]

其中，GLOBAL 表示查看全局变量，SESSION 表示查询会话变量（默认选项，也可以使用 LOCAL 替代），LIKE 用于查找特定名字的变量，WHERE 用于查找满足指定条件的变量。

例如，以下语句返回了当前会话中的所有会话变量：

show variables;

Variable_name                                 |Value                         |
----------------------------------------------|------------------------------|
activate_all_roles_on_login                   |OFF                           |
admin_address                                 |                              |
admin_port                                    |33062                         |
admin_ssl_ca                                  |                              |
admin_ssl_capath                              |                              |
admin_ssl_cert                                |                              |
admin_ssl_cipher                              |                              |
...

以下语句使用 LIKE 运算符返回了所有名字以 version 开头的全局变量：

show global variables like 'version%';

Variable_name          |Value                       |
-----------------------|----------------------------|
version                |8.0.21                      |
version_comment        |MySQL Community Server - GPL|
version_compile_machine|x86_64                      |
version_compile_os     |Linux                       |
version_compile_zlib   |1.2.11                      |

除了使用 SHOW VARIABLES 命令之外，也可为通过 SELECT 语句查询系统变量的值。例如，以下查询返回了全局变量 sql_mode 的设置：

select @@global.sql_mode;

如果某个全局变量没有对应的会话变量，也可以省略 global 关键字，例如：

select @@max_connections;

会话变量的查询方式如下：

select @@session.sql_mode;
select @@local.sql_mode;
select @@sql_mode;

引用系统变量时，需要在变量名前加上两个 @ 符号；下文将会介绍，使用一个 @ 符号进行引用的是用户变量。

📝除了以上方式之外，MySQL 性能模式（Performance Schema）中也提供了几个关于系统变量的数据字典表，具体可以参考官方文档。

17.1.2 设置系统变量

除了使用命令行参数或者选项文件之外，也可以使用 SET 命令动态修改某些系统变量。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 命令可以用于设置各种变量，user_var_name 表示用户定义变量，param_name 是存储过程/函数的参数变量，local_var_name 是存储过程/函数的局部变量，system_var_name 是系统变量；除了使用等号（=）进行赋值以外，也可以使用 := 进行赋值。

对于全局变量，设置时可以在变量前加上 GLOBAL 关键字或者 @@GLOBAL 限定符。例如：

set global max_connections = 1000;
set @@global.max_connections = 1000;

以上语句将全局的最大连接数设置为 1000。需要注意的是，虽然可以使用“select @@max_connections;”查询没有对应会话变量的全局变量，但是不能使用以下方式设置这种全局变量：

set max_connections = 1000;

ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL

📝如果设置了全局变量，新值将会用于初始化随后建立的连接会话。虽然所有会话都能立即看到修改后的全局变量值，但是已有连接的会话变量不会受到影响。

动态修改的全局变量在 MySQL 服务重启之后将会被重置为初始值，如果想要持久化这些设置，可以使用 PERSIST 选项。例如：

set persist max_connections = 1000;
set @@persist.max_connections = 1000;

以上命令会将 max_connections 的设置修改为 1000，同时将该设置写入数据目录下的 mysqld-auto.cnf 配置文件中，MySQL 服务重启时会读取该文件重新设值其中的参数。这种方式比直接修改 my.cnf 更加方便高效，但并非所有的全局变量都支持持久化设置。

除此之外，也可以通过 PERSIST_ONLY 选项进行全局变量的持久化，但是不修改当前的运行时参数。例如：

set persist_only max_connections = 1000;
set @@persist_only.max_connections = 1000;

以上语句只修改 mysqld-auto.cnf 配置文件，再次重启 MySQL 服务时生效，但是不会影响当前运行中的设置。

如果想要删除持久化配置文件中的参数设置，可以使用 RESET PERSIST 语句。例如：

reset persist if exists max_connections;

忽略变量名意味着删除 mysqld-auto.cnf 配置文件中的所有设置。

对于会话变量，设置时可以在变量前加上 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';

修改会话变量只对当前会话有效，不会影响其他连接会话的设置。

如果想要将全局变量设置为编译时的默认值，或者将会话变量设置为对应全局变量的当前值，可以使用 DEFAULT 值。例如：

set @@session.max_join_size = default;
set @@session.max_join_size = @@global.max_join_size;

以上语句可以将会话变量 max_join_size 的值设置为当前全局变量 max_join_size 对应的数值。

17.2 用户变量

当我们需要在不同的 SQL 语句之间传递数据时，可以定义一个变量，然后在某个语句中进行赋值并且在其他语句中进行引用。用户变量的作用范围在当前会话之内，关闭连接时自动销毁；一个会话创建的用户变量不会被其他会话看到或者使用（性能数据库 performance_schema 中的 user_variables_by_thread 表中列出了所有会话定义的用户变量）。

17.2.1 定义用户变量

用户变量使用 @var_name 格式进行定义，其中变量名 var_name 由字母数字、点号（.）、下划线（_）以及美元符号（$）组成，最大长度为 64 个字符。例如，以下都是有效的用户变量：

@id
@123
@.user_var
@$a_very_long_variable_name

如果使用引号进行引用，变量名中也可以使用其他字符。例如：

@'my-var'
@"my-var"
@`my-var`

用户变量的名称不区分大小写，@id 和 @ID 是同一个变量。

用户变量的赋值方法有两种：SET 命令或者 SELECT 语句。首先，SET 命令的语法如下：

SET @var_name = expr [, @var_name = expr] ...

除了使用等号（=）之外，也可以使用 := 进行变量赋值。

用户变量支持以下数据类型：integer、decimal、float、double、binary、char、varchar、text 或者 NULL 值。例如：

set @employee_count = (select count(*) from employee);
set @user_name = 'tony dong';

第一个变量的类型是整数，通过一个子查询进行赋值；第二个变量的类型是字符串，通过一个字面值进行赋值。

用户变量的第二种赋值方式如下：

select @counter := 100;
select @user_name := 'tony dong';

这种设置方式在 MySQL 8.0 中仍然支持，但是将来会被弃用，因此推荐使用 SET 命令进行赋值。另外，这种方式只能使用 := 进行赋值，因为 SELECT 语句中的 = 是一个比较运算符。

如果查询语句返回多行数据，变量只保存最后一行中的数据。例如：

select @emp_name := emp_name
from employee
order by emp_id;

select @emp_name;
@emp_name |
----------|
孙乾       |

变量 @emp_name 中存储的是编号为 25 的员工姓名。

17.2.2 访问用户变量

大多数情况下，用户变量都可以当作表达式用于 SELECT 列表、WHERE 子句、INSERT 或者 UPDATE 等语句中。例如：

set @emp_id = 1;

select @emp_id;

@emp_id|
-------|
      1|

select emp_id, emp_name
from employee
where emp_id = @emp_id;

emp_id|emp_name |
------|---------|
     1|刘备      |

不过，用户变量不能用于明确需要字面值的场景，例如 SELECT 语句的 LIMIT 子句或者 LOAD DATA 语句的 IGNORE N LINES 子句不能出现用户变量。

set @limit_number = 5;

select *
from employee
limit @limit_number;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@limit_number' at line 3

📝访问一个未初始化的变量将会返回 NULL 值，类型为字符串。

在单个语句中设置并访问相同的变量可能会导致非预期的结果，例如：

set @x = 5, @x=@x+1;

select @x;
@x|
--|
0x|

结果显示变量 @x 没有初始化。为了避免这种问题，可以在使用变量之前将其设置为 0 或者 ''。

用户变量的一个常见用途是用于存储最新插入数据对应的自增字段值（ AUTO_INCREMENT）。例如，当我们插入一条订单记录时，需要获取它对应的自增 id 值，然后用于生成订单明细。此时就可以通过用户变量存储最新的订单编号：

-- 生成订单
insert into order(clo1, col2, ...) values ...;

-- 获取订单号
select @order_id:=last_insert_id();

-- 生成订单明细
insert into order_detail(order_id, col2, ...) values (@order_id, ...);

用户变量的作用是存储数据值，不能直接作为 SQL 语句中的标识符（例如表名或者数据库名等）使用，也不能用于替换保留字（例如 SELECT）。例如：

set @col = "emp_name";

select @col
from employee
limit 1;

@col    |
--------|
emp_name|

查询返回的是字符串“emp_name”，而不是员工姓名。如果尝试将变量的值作为标识符使用，将会返回错误。例如：

select `@col`
from employee
limit 1;
ERROR 1054 (42S22): Unknown column '@col' in 'field list'

不过，以上限制存在一个例外情况，就是可以使用变量构造预编译的动态 SQL 语句。例如：

set @col = "emp_name";

set @sql = concat("select ", @col, " from employee limit 1");

prepare stmt from @sql;

execute stmt;
emp_name |
---------|
关兴      |

deallocate prepare stmt;

首先，变量 @col 存储了字段的名称；然后，通过 concat 函数拼接出动态语句 @sql；prepare 用于预编译一个语句，execute 执行预编译语句，deallocate prepare 释放预编译的语句。我们可以查询使用变量拼接之后的语句内容：

select @sql;
@sql                                 |
-------------------------------------|
select emp_name from employee limit 1|

对于预编译语句，用户变量可以替换语句的任何部分。预编译语句的使用将会在后续文章中进行详细介绍。

Default footer
Copyright © 2024 imodou