MySQL变量的应用

写在前面

数据库脚本开发过程，除基本的查询增、删、改、查外，为了更方便于应用，编写更复杂的逻辑，常用的方法还有变量，临时表等。特别是在开发存储过程和函数时。
本文只会对变量进行实际应用的描述。

1.变量

MySQL中变量分为：局部变量，用户变量，会话变量和全局变量。

1. 局部变量，在写存储过程或函数时，只在脚本的begin/end语句模块中使用的变量。
2. 用户变量，无需声明，直接使用。
3. 会话变量就是在客户端建立连接时建立的变量，随着会话的结束，变量也同时消亡。会话变量的变化只影响到当前所建立的会话，即只是在当前客户端可用。
4. 全局变量是会对于整个数据库的，他不会随着某一客户端的建立和消亡而变化。全局变量的修改会影响到所有会话和客户端，即在连接客户端时的默认值。

1.1 查看会话变量和全局变量

会话变量和全局变量为系统层面的变量，可理解为系统配置项。

1.1.1 会话变量

-- 会话变量 --
# 查看会话变量
show session variables;

# 模糊查看指定会话变量
show session variables like 'auto%';  # 查看auto开头的变量

# 查询会话变量值
select @@session.autocommit;  # 查询会话变量autocommit的值

# 修改变量
# 自动提交功能如果关闭，在执行数据表修改操作后，需要再执行commit;命令修改才会生效。
set @@session.autocommit=0;  # 通过修改变量来关闭会对于本次会话的自动提交功能

1.1.2 全局变量

-- 全局变量 --
# 查看全局变量
show global variables;

# 模糊查看指定全局变量
show global variables like 'auto%';  # 查看auto开头的变量

# 查询全局变量值
select @@global.autocommit;  # 查询全局变量autocommit的值

# 修改变量
# 自动提交功能如果关闭，在执行数据表修改操作后，需要再执行commit;命令修改才会生效。
set @@global.autocommit=0;  # 通过修改变量来关闭数据库的自动提交功能

1.2 创建局部变量和用户变量

1.2.1 变量类型

数据库中常用的类型有：

字符型：varchar
整型：int / integer
长整型：long
数字型：numeric
双精度型：double
日期时间型：datetime
时间戳：timestamp

声明变量格式为：

DECLARE 变量名 变量类型 [DEFAULT 预设值]

1.2.2 局部变量

通过【Navicat for MySQL】客户端创建存储过程时，代码中会自动生成一对BEGIN/END，表示一个SQL脚本域。

BEGIN
	#Routine body goes here...

END;

声名变量需要在通过关键字declare来标识。变量的使用参考脚本：

BEGIN
	#Routine body goes here...
	# 定义变量 Total_Row_Count 并设置预设为0
	DECLARE Total_Row_Count INT DEFAULT 0;

	# 给变量赋值为 1
	SET Total_Row_Count := 1;

	# 通过 SELECT 输出变量的值
	SELECT Total_Row_Count;

	# 通过查询数据表，把查询结果赋值给变量
	SELECT Count(*) INTO Total_Row_Count
	FROM country;

	# 通过 SELECT 输出变量的值
	SELECT Total_Row_Count;

END

运行结果：

1.2.3 用户变量

用户变量赋值的格式是：SET @i = 0

# 用户变量无需提前声明
# 赋值用户变量@i初始值为0
SET @i = 0;

# 通过用户变量生成序号
SELECT
	@i :=@i + 1 AS `ID`,
	`Code`,
	`Name`,
	`Continent`
FROM
	country;

执行结果如下所示：

1.2.4 参数

使用存储过程或函数，参数是非常常用的一个功能。
存储过程的DDL（数据库定义语言），存储过程通过参数vContinent，lPopulation将调用时的两个数值传给存储过程进行查询操作。

修饰词IN
输入参数

CREATE PROCEDURE `sp_get_countrys`(IN `vContinent` varchar(30),IN `lPopulation` long)
BEGIN
	#Routine body goes here...

	SELECT *
	FROM country
  WHERE `Continent` = vContinent And `Population` > lPopulation
;
END

通过 **SQL（结构化查询语言）**调用存储过程

Call sp_get_countrys('Asia', 100000000);

可以看到结果是把输入参数作为条件进行的查询

修饰词OUT
输出参数，此参数需要指定一个变量，变量不需初始化，用来接收运行的输出结果。
修改上述脚本，增加一个输出变量，vCount用于返回查询到的记录结果数量

CREATE PROCEDURE `sp_get_countrys`(IN `vContinent` varchar(30),IN `lPopulation` long,OUT `vCount` long)
BEGIN
	#Routine body goes here...

	SELECT *
	FROM country
  WHERE `Continent` = vContinent And `Population` > lPopulation
;

	SELECT COUNT(*) INTO vCount
	FROM country
  WHERE `Continent` = vContinent And `Population` > lPopulation
;
	
END

使用如下脚本调用存储过程，并通过用户变量@CountryCount来接收输出变量的结果

Call sp_get_countrys('Asia',10000000,@CountryCount);
select @CountryCount;

可以查询结果将变成两个结果输出。

结果1 为数据结果
结果2 为OUT变量的输出结果，查询数据结果数量

2 总结

变量类似于高级开发语言如JAVA、C#、VB等，只要是编程语言都是一项最基本的入门必学的知识，存储过程或函数开发过程中也同样是一项不可缺少的知识。每个想成为码农的有志小青年，都应该牢牢的把这项知识掌握到位。

---

就让我们一起

努力，奋斗，内卷吧！