（转）MySQL中全局变量、会话变量、用户变量和局部变量的区别

2022-08-13 14:37:28

原文：https://www.modb.pro/db/12232

前言

之前在项目的存储过程中发现有通过 DECLARE 关键字定义的变量如DECLARE cnt INT DEFAULT 0;，还有形如 @count 这样的变量，存储过程中拿过来直接就进行设置，像这样set @count=1;，这两种类型的变量究竟有什么区别却弄不清楚，赶紧上网查询资料，发现还有@@sql_mode这样的变量，这一个圈俩圈的到底是什么啊？会不会出现三个圈的情况？

变量分类与关系

经过一段时间学习和测试，再配合官方的文档，现在大致弄清楚了这些变量的区别，一般可以将MySQL中的变量分为全局变量、会话变量、用户变量和局部变量，这是很常见的分类方法，这些变量的作用是什么呢？可以从前往后依次看一下。

首先我们知道MySQL服务器维护了许多系统变量来控制其运行的行为，这些变量有些是默认编译到软件中的，有些是可以通过外部配置文件来配置覆盖的，如果想查询自编译的内置变量和从文件中可以读取覆盖的变量可以通过以下命令来查询:

mysqld --verbose --help

如果想只看自编译的内置变量可以使用命令：

mysqld --no-defaults --verbose --help

接下来简单了解一下这几类变量的应用范围，首先MySQL服务器启动时会使用其软件内置的变量（俗称写死在代码中的）和配置文件中的变量（如果允许，是可以覆盖源代码中的默认值的）来初始化整个MySQL服务器的运行环境，这些变量通常就是我们所说的全局变量，这些在内存中的全局变量有些是可以修改的。

当有客户端连接到MySQL服务器的时候，MySQL服务器会将这些全局变量的大部分复制一份作为这个连接客户端的会话变量，这些会话变量与客户端连接绑定，连接的客户端可以修改其中允许修改的变量，但是当连接断开时这些会话变量全部消失，重新连接时会从全局变量中重新复制一份。

其实与连接相关的变量不只有会话变量一种，用户变量也是这样的，用户变量其实就是用户自定义变量，当客户端连接上MySQL服务器之后就可以自己定义一些变量，这些变量在整个连接过程中有效，当连接断开时，这些用户变量消失。

局部变量实际上最好理解，通常由DECLARE 关键字来定义，经常出现在存储过程中，非常类似于C和C++函数中的局部变量，而存储过程的参数也和这种变量非常相似，基本上可以作为同一种变量来对待。

变量的修改

先说全局变量有很多是可以动态调整的，也就是说可以在MySQL服务器运行期间通过 SET 命令修改全局变量，而不需要重新启动 MySQL 服务，但是这种方法在修改大部分变量的时候都需要超级权限，比如root账户。

相比之下会话对变量修改的要求要低的多，因为修改会话变量通常只会影响当前连接，但是有个别一些变量是例外的，修改它们也需要较高的权限，比如 binlog_format 和 sql_log_bin，因为设置这些变量的值将影响当前会话的二进制日志记录，也有可能对服务器复制和备份的完整性产生更广泛的影响。

至于用户变量和局部变量，听名字就知道，这些变量的生杀大权完全掌握在自己手中，想改就改，完全不需要理会什么权限，它的定义和使用全都由用户自己掌握。

测试环境

以下给出MySQL的版本，同时使用root用户测试，这样可以避免一些权限问题。

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

变量查询与设置

全局变量

这些变量来源于软件自编译、配置文件中、以及启动参数中指定的变量，其中大部分是可以由root用户通过 SET 命令直接在运行时来修改的，一旦 MySQL 服务器重新启动，所有修改都被还原。如果修改了配置文件，想恢复最初的设置，只需要将配置文件还原，重新启动 MySQL 服务器，一切都可以恢复原来的样子。

查询

查询所有的全局变量：

show global variables;

一般不会这么用，这样查简直太多了，大概有500多个，通常会加个like控制过滤条件：

mysql> show global variables like 'sql%';
+------------------------+----------------------------------------------------------------+
| Variable_name          | Value                                                          |
+------------------------+----------------------------------------------------------------+
| sql_auto_is_null       | OFF                                                            |
| sql_big_selects        | ON                                                             |
| sql_buffer_result      | OFF                                                            |
| sql_log_off            | OFF                                                            |
| sql_mode               | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes              | ON                                                             |
| sql_quote_show_create  | ON                                                             |
| sql_safe_updates       | OFF                                                            |
| sql_select_limit       | 18446744073709551615                                           |
| sql_slave_skip_counter | 0                                                              |
| sql_warnings           | OFF                                                            |
+------------------------+----------------------------------------------------------------+
11 rows in set, 1 warning (0.00 sec)

mysql>

还有一种查询方法就是通过select语句：

select @@global.sql_mode;

当一个全局变量不存在会话变量副本时也可以这样

select @@max_connections;

设置

设置全局变量也有两种方式：

set global sql_mode='';

或者

set @@global.sql_mode='';

会话变量

这些变量基本来自于全局变量的复制，与客户端连接有关，无论怎样修改，当连接断开后，一切都会还原，下次连接时又是一次新的开始。

查询

类比全局变量，会话变量也有类似的查询方式，查询所有会话变量

show session variables;

添加查询匹配，只查一部分会话变量：

show session variables like 'sql%';

查询特定的会话变量，以下三种都可以：

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

设置

会话变量的设置方法是最多的，以下的方式都可以：

set session sql_mode = '';
set local sql_mode = '';
set @@session.sql_mode = '';
set @@local.sql_mode = '';
set @@sql_mode = '';
set sql_mode = '';

用户变量

用户变量就是用户自己定义的变量，也是在连接断开时失效，定义和使用相比会话变量来说简单许多。

查询

直接一个select语句就可以了：

select @count;

设置

设置也相对简单，可以直接使用set命令：

set @count=1;
set @sum:=0;

也可以使用select into语句来设置值，比如：

select count(id) into @count from items where price < 99;

局部变量

局部变量通常出现在存储过程中，用于中间计算结果，交换数据等等，当存储过程执行完，变量的生命周期也就结束了。

查询

也是使用select语句：

declare count int(4);
select count;

设置

与用户变量非常类似：

declare count int(4);
declare sum int(4);
set count=1;
set sum:=0;

也可以使用select into语句来设置值，比如：

declare count int(4);
select count(id) into count from items where price < 99;

其实还有一种存储过程参数，也就是C/C++中常说的形参，使用方法与局部变量基本一致，就当成局部变量来用就可以了

几种变量的对比使用

操作类型	全局变量	会话变量	用户变量	局部变量（参数）
文档常用名	global variables	session variables	user-defined variables	local variables
出现的位置	命令行、函数、存储过程	命令行、函数、存储过程	命令行、函数、存储过程	函数、存储过程
定义的方式	只能查看修改，不能定义	只能查看修改，不能定义	直接使用，`@var`形式	`declare count int(4);`
有效生命周期	服务器重启时恢复默认值	断开连接时，变量消失	断开连接时，变量消失	出了函数或存储过程的作用域，变量无效
查看所有变量	`show global variables;`	`show session variables;`	-	-
查看部分变量	`show global variables like 'sql%';`	`show session variables like 'sql%';`	-	-
查看指定变量	`select @@global.sql_mode`、 `select @@max_connections;`	`select @@session.sql_mode;`、 `select @@local.sql_mode;`、 `select @@sql_mode;`	`select @var;`	`select count;`
设置指定变量	`set global sql_mode='';`、 `set @@global.sql_mode='';`	`set session sql_mode = '';`、 `set local sql_mode = '';`、 `set @@session.sql_mode = '';`、 `set @@local.sql_mode = '';`、 `set @@sql_mode = '';`、 `set sql_mode = '';`	`set @var=1;`、 `set @var:=101;`、 `select 100 into @var;`	`set count=1;`、 `set count:=101;`、 `select 100 into count;`

相信看了这个对比的表格，之前的很多疑惑就应该清楚了，如果发现其中有什么疑惑的地方可以给我留言，或者发现有什么错误也可以一针见血的指出来，我会尽快改正的。

总结

MySQL 中的变量通常分为：全局变量、会话变量、用户变量、局部变量
其实还有一个存储过程和函数的参数，这种类型和局部变量基本一致，当成局部变量来使用就行了
在表格中有一个容易疑惑的点就是无论是全局变量还是会话变量都有select@@变量名的形式。
select@@变量名这种形式默认取的是会话变量，如果查询的会话变量不存在就会获取全局变量，比如@@max_connections
但是SET操作的时候，set @@变量名=xxx 总是操作的会话变量，如果会话变量不存在就会报错

码农公寓

前言