放弃前戏，直奔主题

什么是变量？有点编程基础的盆友都知道啥是变量哈！笔者在此就不啰嗦了。放弃前戏，直接步入主题。那么，在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.自定义局部变量

写在最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

放弃前戏，直奔主题

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

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

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

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

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

3. 查看指定系统变量值

4. 修改系统变量的值

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

1.自定义会话用户变量

2.自定义局部变量

写在最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像