15. Mysql 变量的使用

变量的概述

MySQL支持不同类型的变量，包括自定义变量、系统变量和局部变量。自定义变量是在会话中定义的变量，用于存储临时数据。系统变量是MySQL服务器提供的全局变量，用于控制服务器的行为。局部变量是存储过程或函数中使用的变量，它们的作用范围仅限于该存储过程或函数。

变量常用于存储和操作数据，执行复杂的计算和逻辑操作，控制查询的执行流程，以及传递数据和结果等。

自定义变量

自定义变量是用户根据所需而自建的变量，该变量特别有用，常用语固定的查询中，只要传入该变量就能得到所需要的结果。为了编码规范，自定义变量以一个“@” 开头。

用户定义变量的作用范围是会话级别。这意味着当一个会话结束时，变量的值将被清除。如果需要在不同的会话中使用变量，则需要使用全局变量。

用户定义变量可以存储不同类型的数据，包括整数、浮点数、字符串等。变量的数据类型是根据赋给变量的值自动确定的。

示例

简单变量赋值

# 定义变量
set @a = 1;
set @b := 2;
set @c := @a + @b;
# 查询变量
select @a,@b,@c,@c+3;
+------+------+------+------+
| @a   | @b   | @c   | @c+3 |
+------+------+------+------+
|    1 |    2 |    3 |    6 |
+------+------+------+------+
# 更新之前变量值，常用于循环中
set @a = @a + 1;

变量实现排名

select student_id,subject,score,`row_number`
from (select a.student_id,a.subject,a.score,if(@pre_subject = a.subject,   -- 排序后，判断上一行的学科是否等于当前行@row_num := @row_num + 1,   -- 是，返回@row_num+1，并且再次赋值给该变量@row_num@row_num := 1) `row_number`,-- 否，重置@row_num变量为1@pre_subject := a.subject      -- 将当前行的学科赋值给@pre_subjectfrom student_subject_scroe as a,(select @pre_subject := null, @row_num := 0) b -- 临时表b，定义初始变量order by subject, score desc  -- 根据学科，分数降序排
) as c;

上面是变量实现排名的一种情况，如果想了解更多可以查看：Mysql 常见排名实现。

系统变量

系统变量定义了当前MySQL服务实例的属性、特征，可以简单理解成是系统定义好的变量。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数值。

按作用域分类，系统变量可以分为全局变量（需要添加 global 关键字）以及会话变量（需要添加 session 关键字），简单理解就是会话变量仅在当前会话生效，而全局变量则一直生效。

查看系统变量

# 查看所有全局变量
show global variables;
# 查看所有会话变量
show session variables;
show variables;
# 查看条件下的系统变量。
show global variables like '%version%';
# 查看条件下的会话变量
show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
# 查看指定变量
select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
select @@global.time_zone,@@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

系统变量赋值

全局变量的赋值：set global 变量名 = 值;

会话变量的赋值：set 变量名 = 值; 或者set @@变量名 = 值。

@@首先标记会话变量，如果会话变量不存在，则标记全局变量。

示例

设置时区：设置为北京时间，也就是我们所在的东8区。

# 方法一：刷新后能用，服务器重启会丢失
set global time_zone = ‘+8:00’;
set time_zone = ‘+8:00’;
flush privileges;
# 方法二：修改配置文件 /etc/my.cnf
[mysqld]
default-time_zone = '+8:00'

设置连接次数

set global max_connections = 161;
set max_connections = 161;
set @@global.max_connections = 161;

以上是常见的两个系统变量修改，如果遇到其他变量要修改，方法也是类似。

局部变量

在存储过程或函数中，可以使用DECLARE语句定义局部变量。局部变量只在存储过程或函数的作用域内有效。可以使用局部变量进行计算、逻辑判断和存储数据等操作。

示例：

简单的存储过程

create procedure myprocedure()
begindeclare a int;declare b int default 0;declare c varchar(255);set a = 10;select a,b,c;-- 其他逻辑操作
end;

带有逻辑判断的存储过程

create procedure myprocedure()
begindeclare a int;set a = 10;if a > 5 then-- 执行某些操作end if;-- 其他逻辑操作
end;

总结

通过常见的几种变量介绍，可以发现变量不仅可以用来存储临时数据，还可以控制服务器行为，进一步提供高级的数据处理和计算能力，而且在控制着查询的执行流程和动态查询中非常实用，比如定义时间变量，写好执行sql脚本或者存储过程，通过创建事件定时任务或者python每日定时执行，即可实现动态数据库操作。

参考资料

• MySQL官方文档：https://dev.mysql.com/doc/
• MySQL Tutoria：https://www.mysqltutorial.org/mysql-variables/
• MySQL 变量实现排名：https://blog.csdn.net/weixin_50357986/article/details/134277584