放弃前戏，直奔主题

什么是变量？有点编程基础的盆友都知道啥是变量哈！笔者在此就不啰嗦了。放弃前戏，直接步入主题。那么，在MySQL数据库中，mysql的变量是什么鬼？简单的说：可以在存储过程或函数中，使用变量来存储值，比如存储计算后的中间结果数据等等，mysql的变量又分为“系统变量”以及“用户自定义变量”。

MySQL服务器维护许多配置其操作的系统变量，以下官方文档描述了这些变量的含义：

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

每个系统变量都有一个默认值。可以在服务器启动时使用命令行或在配置文件（my.cnf）中定义选项来设置系统变量。其中在Mysql运行期间可以通过SET语句在服务器运行时动态更改，这样您就可以修改服务器的操作，而无需停止和重新启动它。您还可以在表达式中使用系统变量值。

想知道一个变量的含义，可以参考官方资料哈

打开官方文档，服务器选项、系统变量和状态变量参考链接：https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
如下图：

先大处着眼一下，总结下变量的分类

总结1：变量分为两大类，系统变量和状态变量。

总结2：系统变量又分为全局系统变量和全局会话变量。

系统变量

系统变量就是Mysql针对自己程序运行的一些参数配置。例如通过系统变量我们可以指定诸如允许同时连入的客户端数量、客户端和服务器通信方式、表的默认存储引擎、查询缓存的大小等设置项。

然后，系统变量还又分2类呢，如下：

GLOBAL：全局系统变量，影响服务器的整体操作。
SESSION：全局会话变量，影响某个客户端连接的操作。(注：SESSION有个别名叫LOCAL)

状态变量

MySQL服务器程序中维护了好多关于程序运行状态的变量，它们被称为状态变量，由于状态变量是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置，我们程序员是不能设置的。与系统变量类似，状态变量也有GLOBAL和SESSION两个作用范围的。比方说Threads_connected表示当前有多少客户端与服务器建立了连接，Handler_update表示已经更新了多少行记录。

查看状态变量的语法：

SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

例如通过全局状态变量Slow_queries，可查询到当前系统中有多少条慢查询记录

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

开始动手实战，二话不说就是撸！

撸什么？笔者带你撸sql，简单的实战，抛砖引玉哈！

一、系统变量之剖析和实战

变量由系统定义，不是用户定义，属于服务器层面
系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.cnf、my.ini等）中的参数值。
系统变量分为全局系统变量（需要添加 global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。

在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。

1.全局系统变量（简称：全局变量）

案例：查看所有全局变量

mysql> show global variables\G;

案例：查看满足条件的部分系统变量

mysql> SHOW GLOBAL VARIABLES LIKE 'admin_%';
+------------------------+-------------------------------+
| Variable_name          | Value                         |
+------------------------+-------------------------------+
| admin_address          |                               |
| admin_port             | 33062                         |
| admin_ssl_ca           |                               |
| admin_ssl_capath       |                               |
| admin_ssl_cert         |                               |
| admin_ssl_cipher       |                               |
| admin_ssl_crl          |                               |
| admin_ssl_crlpath      |                               |
| admin_ssl_key          |                               |
| admin_tls_ciphersuites |                               |
| admin_tls_version      | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+------------------------+-------------------------------+
11 rows in set (0.01 sec)
mysql>

2.会话系统变量（简称：local变量）

每一个MySQL客户端成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。如下图：

特别声明：上图摘自互联网，仅作参考之用，如有侵权请告知笔者，将会立刻删除，谢谢！

全局系统变量针对于所有会话（连接）有效，但不能跨重启
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

案例：查看所有会话变量

mysql> show session variables\G;
# 或者
mysql> show variables\G;

案例：查看满足条件的部分会话变量

mysql> SHOW SESSION VARIABLES LIKE 'admin_%';
+------------------------+-------------------------------+
| Variable_name          | Value                         |
+------------------------+-------------------------------+
| admin_address          |                               |
| admin_port             | 33062                         |
| admin_ssl_ca           |                               |
| admin_ssl_capath       |                               |
| admin_ssl_cert         |                               |
| admin_ssl_cipher       |                               |
| admin_ssl_crl          |                               |
| admin_ssl_crlpath      |                               |
| admin_ssl_key          |                               |
| admin_tls_ciphersuites |                               |
| admin_tls_version      | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+------------------------+-------------------------------+
11 rows in set (0.01 sec)
mysql>

3. 查看指定系统变量值

作为 MySQL 编码规范，MySQL 中的系统变量以两个“@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。 “@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

案例：系统变量admin_tls_version的值

mysql> select @@global.admin_tls_version;
+-------------------------------+
| @@global.admin_tls_version    |
+-------------------------------+
| TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+-------------------------------+
1 row in set (0.00 sec)

案例：查看会话变量windowing_use_high_precision的值

mysql> select @@session.windowing_use_high_precision;
+----------------------------------------+
| @@session.windowing_use_high_precision |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>

4. 修改系统变量的值

有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。具体方法：

方式1：修改MySQL配置文件（如my.cnf、my.ini），继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值，使用该SET语句动态更改，这使您能够修改服务器的操作而无需停止和重新启动它

最佳实践：修改Mysql系统变量的最佳实践是，如果mysql正在运行，且不能重启，但又要修改变量，可用set命令进行修改，就可以不用重启mysql服务而使其马上生效，为了预防忘记，同时还要写入到my.cnf配置文件中，这样的话，等到可以重启mysql的时候，就会从my.cnf配置文件中加载配置了。

全局变量赋值语法格式：

#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;

会话变量赋值语法格式：

#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;

二、用户自定义变量简单剖析和实战

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL中的用户变量以一个 “@” 开头。根据作用范围不同，又分为会话用户变量和局部变量。

会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

1.自定义会话用户变量

变量的定义，语法格式：

#方式1：“=”或“:=” 
SET @用户变量 = 值; 
SET @用户变量 := 值; 
#方式2：“:=” 或 INTO关键字 
SELECT @用户变量 := 表达式 [FROM 等子句]; 
SELECT 表达式 INTO @用户变量 [FROM 等子句];

查看用户变量的值语法：

SELECT @用户变量;

案例1：自定义变量name，并将字符串"chavis"赋值给该变量

# 定义变量和赋值
mysql> set @name="chavis";
Query OK, 0 rows affected (0.00 sec)
# 查询
mysql> select @name;
+--------+
| @name  |
+--------+
| chavis |
+--------+
1 row in set (0.00 sec)
mysql>

案例2：将数字101赋值给变量n，注意，数字值类型一定要使用”:=“进行赋值，不能用”=“

mysql> select @n := 101;
+-----------+
| @n := 101 |
+-----------+
|       101 |
+-----------+
1 row in set, 1 warning (0.00 sec)
# 查询变量
mysql> select @n;
+------+
| @n   |
+------+
|  101 |
+------+
1 row in set (0.00 sec)
mysql>

案例3：随便查询一个没有声明的变量，不会报错，只是值会是NULL

mysql> select @a;
+------------+
| @a         |
+------------+
| NULL       |
+------------+
1 row in set (0.12 sec)

案例4：统计history表有多少条记录，并将统计后的值并赋值给自定义变量abc

# 查询和赋值
mysql> select @abc := count(*) from zabbix.history;
+------------------+
| @abc := count(*) |
+------------------+
|          1274372 |
+------------------+
1 row in set, 1 warning (5.79 sec)
# 查询变量的值
mysql> select @abc;
+---------+
| @abc    |
+---------+
| 1274372 |
+---------+
1 row in set (0.00 sec)
mysql>

案例5：查询hosts表中name为zbxproxy01的记录，并将字段name的值赋给变量@hname

# 查询和赋值
mysql> select name into @hname from zabbix.hosts where name='zbxproxy01';
Query OK, 1 row affected (0.02 sec)
# 查询变量值
mysql> select @hname;
+------------+
| @hname     |
+------------+
| zbxproxy01 |
+------------+
1 row in set (0.00 sec)
mysql>

案例6：可以同时将多个字段值分别赋值给不同的变量，一一对应即可

# 查询和赋值
mysql> select name,hostid into @hname,@hid from zabbix.hosts where name='zbxproxy01';                
Query OK, 1 row affected (0.00 sec)
mysql> select @hname;
+------------+
| @hname     |
+------------+
| zbxproxy01 |
+------------+
1 row in set (0.00 sec)
# 查询变量值
mysql> select @hid;
+-------+
| @hid  |
+-------+
| 10357 |
+-------+
1 row in set (0.00 sec)
mysql>

2.自定义局部变量

定义：可以使用 DECLARE 语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN ... END 中有效，也就是在存储过程或存储函数中的 BEGIN ... END
位置：只能放在 BEGIN ... END 中，而且只能放在第一句

语法格式如下：

BEGIN
    #声明局部变量
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; 
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值]; 
    #为局部变量赋值 
    SET 变量名1 = 值; 
    SELECT 值 INTO 变量名2 [FROM 子句]; 
    #查看局部变量的值 
    SELECT 变量1,变量2,变量3; 
END

定义变量

DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

变量赋值方式1：一般用于赋简单的值

SET 变量名=值;
SET 变量名:=值;

方式2：一般用于赋表中的字段值

SELECT 字段名或表达式 INTO 变量名 FROM 表;

使用变量（查看、比较、运算等）

SELECT 局部变量名;

实践例子

案例：声明局部变量hostname和ipaddr，并将查询到的结果通过into赋值给局部变量hostname和ipaddr，最好通过select进行输出

# 修改结束符为“//”
delimiter //
# 创建存储过程get_hostname_and_ip
create procedure zabbix.get_hostname_and_ip()
begin
declare hostname varchar(64);
declare ipaddr varchar(64);
select host.name,host.ip into hostname,ipaddr from (select h.name,i.ip from zabbix.hosts h join zabbix.interface i on h.hostid=i.hostid and h.name='zbxser01') host; 
select hostname,ipaddr;
end //
# 将结束符修改回默认“;”
delimiter ;
# 调用存储过程
mysql> call zabbix.get_hostname_and_ip;
+----------+----------------+
| hostname | ipaddr         |
+----------+----------------+
| zbxser01 | 192.168.11.153 |
+----------+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

案例：声明两个变量，求和并打印（分别使用会话用户变量、局部变量的方式实现）

方式1：使用会话用户变量

mysql> set @x=3;
mysql> set @y=5;
mysql> set @sum=@x+@y;
Query OK, 0 rows affected (0.00 sec)
mysql> select @sum;
+------+
| @sum |
+------+
|    8 |
+------+
1 row in set (0.00 sec)
mysql>

方式2：在存储过程中使用局部变量

# 修改结束符
delimiter //
# 创建存储过程
CREATE procedure p_add_value()
BEGIN
DECLARE x INT DEFAULT 3;
DECLARE y INT DEFAULT 5;
DECLARE sum INT;
set sum=x+y;
select sum;
END //
# 修改回默认的结束符
delimiter ;
# 调用
mysql> call p_add_value;
+------+
| sum  |
+------+
|    8 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

写在最后

运维是一门实战性非常强的学科，必须动手实践，光看是学不会的。望有兴趣的盆友多动手、多实践哈！当然，背后的原理也需要搞懂，但一开始笔者不建议直接陷入原理的环节，先动手把环境搞起来再说！简单的入了门之后，再分块剖析原理并结合实践，真正做到理论指导实践，实践验证理论的真理。好了，今晚时间有限，就此搁笔，望多多支持、关注、点赞、转发。

一文带你简单搞懂MySQL变量，今晚可以助眠哦！

放弃前戏，直奔主题

先大处着眼一下，总结下变量的分类

开始动手实战，二话不说就是撸！

一、系统变量之剖析和实战

1.全局系统变量（简称：全局变量）

2.会话系统变量（简称：local变量）

3. 查看指定系统变量值

4. 修改系统变量的值

二、用户自定义变量简单剖析和实战

1.自定义会话用户变量

2.自定义局部变量

写在最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像