在MySQL数据库的存储过程和函数中,可以使用变量来存储查询或计算的中间结果数据,或者输出最终的结果数据

在MySQL数据库中,变量分为 系统变量以及用户自定义变量

系统变量

系统变量有系统定义,属于服务器层面/这些系统变量的值要么是编译MySQL时参数的默认值,要么是配置文件(my.ini)中的参数值;可以通过连接查看https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

系统变量分类

系统变量分为全局系统变量(需要添加global关键字)以及会话系统变量(需要添加session关键字);

全局系统变量也称为全局变量,会话系统变量也称为local变量;如果不写,默认会话级别;静态变量(在MySQL服务实例运行期间他们的值不能使用set动态修改)属于特殊的全局系统变量.

全局系统变量针对于所有会话(连接)有效,但不能跨重启
会话系统变量仅针对于当前会话(连接)有效,会话期间,当前会话对某个会话系统变量值的修改,不会影响其他会话同一个会话系统变量的值
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改

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

查看系统变量

查看所有或部分系统变量

-- 查看所有全局变量
SHOW GLOBAL VARIABLES;

-- 查看所有会话变量
SHOW SESSION VARIABLES;
或者
SHOW VARIABLES;

-- 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%标识符%';

-- 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';

查看指定系统变量

作为MySQL 编码规范,MySQL中的系统变量以两个@开头,其中@@global仅用于标记全局系统变量,@@session仅用于标记会话系统变量

@@首先标记会话系统变量,如果会话系统变量不存在,则标记全局系统变量
1
2
3
4
5
6
7
-- 查看指定的系统变量的值
SELECT @@global.变量名;

-- 查看指定的会话变量的值
SELECT @@session.变量名;
或者
SELECT @@变量名;

修改系统变量的值

有些时候,数据库管理员需要修改系统变量的默认值,以便修改当前会话或者MySQL服务实例的属性特性

方式一: 修改MySQL配置文件继而修改MySQL系统变量的值(该方法需要重启MySQL服务)
方式二: 在MySQL服务运行期间,使用”SET”命令重新设置系统变量的值

-- 为某个系统变量赋值
-- 方式1
SET @@global.变量名=变量值;
-- 方式2
SET GLOBAL 变量名=变量值;

-- 为某个会话变量赋值
-- 方式1
SET @@session.变量名=变量值;
--方式2
SET SESSION 变量名=变量值;

用户变量

用户变量分类

用户变量是用户自己定义的,作为MySQL编码规范,MySQL的用户变量以一个"@"开头

根据作用范围不同,分为会话用户变量和局部变量

会话用户变量: 作用域和会话变量一样,只对当前连接会话有效
局部变量: 只在 begin和end语句块中有效,局部变量只能在存储过程和函数中使用

会话用户变量

变量定义

-- 方式一: "="或者":="
SET @用户变量 = 值;
SET @用户变量 := 值;

-- 方式二: ":="或者 INTO 关键字
SELECT @用户变量 := 表达式 [FROM 等子句]
SELECT 表达式 INTO @用户变量 [FROM 等子句]

查看用户变量的值 (查看,比较,运算等)

-- 结构语句
SELECT @用户变量

-- 举例
SET @a = 1;
SELECT @a;

SELECT @num := COUNT(*) FROM employees;
SELECT @num;

SELECT @big; -- 查看某个未声明的变量时,将得到 NULL 值

局部变量

定义方式: 使用DECLARE语句定义一个局部变量

作用域: 仅仅在定义它的BEGIN ... END中有效

位置: 只能放在BEGIN ... END中,而且只能放在第一句

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

定义变量

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

变量赋值

-- 方式一: 一般用于赋简单的值
SET 变量名 = 值;
SET 变量名 := 值;

-- 方式二: 一般用于赋表中的字段值
SELECT 字段名或者表达式 INTO 变量名 FROM 表;

使用变量 (查看,比较,运算等)
1
SELECT 局部变量名;

对比会话用户变量和局部变量

	作用域	定义位置	语法
会话用户变量	当前会话	会话的任何地方	加@符号,不用指定类型
局部变量	定义它的BEGIN .. END中	BEGIN .. END 的第一句	一般不用加@符号,需要指定类型

定义条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题,处理程序定义了在遇到问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能够继续执行.这样可以增强程序处理问题的能力,避免程序异常停止运行

说明: 定义条件和处理程序在存储过程,存储函数中都是支持的

案例分析

案例分析: 创建一个名称为 “UpdateDataNoCondition”的存储过程;代码如下

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
	SET @x = 1;
	UPDATE employees SET email = NULL WHERE last_name = 'Abel';
	SET @x = 2;
	UPDATE employees SET email = 'abc' WHERE last_name = 'Abel';
	SET @x = 3;
	
END //

DELIMTER ;

-- 调用存储过程
CALL UpdateDataNoCondition();
-- 执行报错 ERROR 1048 (23000): Column 'email' cannot be null

-- 查看执行结果
SELECT @X;
-- 结果  x = 1

结论: 此时@x变量的值为1.结合创建存储过程的sql语句代码可以得出:储存过程中未定义条件和处理程序,且当存储过程中执行的sql语句报错时,mysql数据库会抛出错误,并退出当前sql逻辑,不再向下继续执行

定义条件

定义条件就是给mysql中的错误码命名,这有助于存储的程序代码更清晰.它将一个错误名字和指定的错误条件关联起来.这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中

-- 定义条件 使用 DECLARE 语句,语法格式:
DECLARE 错误名称 CONDITION FOR 错误码(或错误条件);

错误码说明

MySQL_error_code和sqlstate_value都可以表示MySQL的错误
- MySQL_error_code 是数值类型错误代码
- sqlstate_value 是长度为5的字符串类型错误代码
例如: 在ERROR 1418 (HY00) 中, 1418 是MySQL_error_code, “HY000”是sqlstate_value

举例

-- 定义 "Field_Not_Be_NULL" 错误名与MySQL中违反非空约束的错误类型是"ERROR 1048 (2300)"对应
-- 使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

-- 使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE "23000";

定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序.定义处理程序时,使用 DECLARE 语句

-- 语法结构
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;

处理方式: 处理方式有3个取值: CONTINUE, EXIT, UNDO.
- CONTINUE: 表示遇到错误不处理,继续执行
- EXIT: 表示遇到错误马上退出
- UNDO: 表示遇到错误后撤回之前的操作.MySQL中暂时不支持这样的操作.
错误类型(即条件) 可以有如下取值:
- SQLSTATE '字符串错误码': 表示长度为5的sqlstate_value类型的错误代码
- MySQL_error_code: 匹配数值类型错误代码
- 错误名称: 表示 DECLARE … CONDITION 定义的错误条件名称
- SQLWARNING: 匹配所有以 01 开头的 SQLSTATE 错误代码
处理语句: 如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句.语句可以是像 SET 变量 = 值这样简单语句,也可以是使用BEGIN ... END编写的复合语句

实例

-- 方式一: 捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

-- 方式二: 捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

-- 方式三: 先定义条件,再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SHCH_TABLE SET @info = 'NO_SUCH_TABLE';

-- 方式四: 使用sqlwarning
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

-- 方式五: 使用 NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

-- 方式六: 使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

案例解决

在存储过程中,定义处理程序,捕获sqlstate_value值,当遇到MySQL_error_code值为1048时,执行CONTINUE操作,并且将@proc_value值设置为-1

-- 创建存储过程,加入处理程序
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition ()
	BEGIN
		-- 定义处理程序
        DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel';
        SET @x = 2;
        UPDATE employees SET email = 'abc@126.com' WHERE last_name = 'Abel';
        SET @x = 3;
END //
DELIMITER ;

-- 调用存储过程
CALL UpdateDataNoCondition();
-- 查看变量
SELECT @x,@proc_value;
+------+-------------+
| @x | @proc_value |
+------+-------------+
| 3  | -1          |
+------+-------------+

流程控制

分支结构之IF

IF语句的语法结构

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2] ...
[ELSE 操作N]
END IF

根据表达式的结果为TRUE或者FALSE执行相应的语句,’[]’中的内容是可选的

特点: 1不同的表达式对应不同的操作;2使用在begin end 中

-- 举例
IF val IS NULL
	THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;

分支结构之CASE

CASE语句的语法结构

-- 情况一: 类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或者语句1(如果是语句,需要加分号)
WHEN 值2 THEN 结果2或者语句2(如果是语句,需要加分号)
...
ELSE 结果n或者语句n(如果是语句,需要加分号)
END [case] (如果是放在begin end 中需要加上case,如果放在SELECT后面不需要)

-- 情况二: 类似于多重if
CASE
WHEN 条件1 THEN 结果1或者语句1(如果是语句,需要加分号)
WHEN 条件2 THEN 结果2或者语句2(如果是语句,需要加分号)
...
ELSE 结果n或者语句n(如果是语句,需要加分号)
END [CASE] (如果是放在 begin end 中需要加上 CASE ,如果放在select中则不需要)

-- 举例
CASE val
	WHEN 1 THEN SELECT 'val is 1';
	WHEN 2 THEN SELECT 'val is 2';
	ELSE SELECT 'val is not 1 or 2';
END CASE;

MySQL变量、流程控制、游标