【MySQL】变量与报错处理

一、变量

在MySQL的存储过程与函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。它可以分为用户自定义变量与系统变量

1、系统变量

1）系统变量分为全局变量（需要使用关键字global）和会话系统变量（需要使用关键字session），如果没有声明global，那默认是session级别的变量

• 全局系统变量对所有会话（连接）都有效，但是重启后就会重置（但是在MySQL8.0中，可以使用关键字PERSIST进行持久化，也就是SET GLOBAL 变量名 = 变量值;的形式，即使MySQL服务器重启，这个全局变量也不会变回去）
• 会话系统变量仅针对当前会话（连接）有效。会话期间，当前会话对某个会话系统变量的修改，并不会影响其他会话中同一会话系统变量的值。而且会话重新连接之后，变量值也会重置
• 会话1修改了某个全局系统变量会影响到会话2中同一全局系统变量

2）MySQL中有些变量只能是全局系统变量，例如max_connection用于限制服务器的最大连接数
有些系统变量既可以是全局的又可以是会话级别的，如character_client_set用于设置客户端的字符集
有些系统变量只能是会话级别的，例如pseudo_thread_id用于标记当前会话的MySQL连接id

3）查看系统变量

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

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

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

# 查看满足条件的部分会话系统变量
SHOW SESSION VARIABLES LIKE '%标识符%';
或者
SHOW VARIABLES LIKE '%标识符%';

# 查看指定全局系统变量
SELECT @@global.变量名;

# 查看指定会话系统变量
SELECT @@session.变量名;
或者
SELECT @@变量名;  #不声明全局还是会话级别的情况下，会先去会话系统变量中找，如果没有再去全局系统变量中找

4）修改系统变量

• 方式一：修改MySQL的配置文件，但是需要重启服务
• 方式二：使用set命令重新设置系统变量的值

# 修改全局系统变量值
SET @@global.变量名 = 变量值;
或者
SET GLOBAL 变量名 = 变量值;

# 修改会话系统变量值
SET @@session.变量名 = 变量值;
或者
SET SESSION 变量名 = 变量值;

2、用户变量

1）用户变量是用户自己定义的，MySQL中用户变量以@开头进行定义。根据作用范围的不同分为会话用户变量和局部变量

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

2）会话用户变量的使用

声明和赋值

# 方式一：使用=或者:=
SET @用户变量 = 值;
SET @用户变量 := 值;

# 方式二：使用:=或者INTO关键字，就相当于在查询语句中插入一段用户变量的声明，方式二不能使用=
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
#例如
SELECT @A1 := COUNT(*) FROM t_test;
SELECT AVG(salary) INTO @A2 FROM t_employee;

查询

SELECT @用户变量;

3）局部变量

• 相关关键字：使用关键字DECLARE进行定义，使用SET进行赋值，使用SELECT进行查询
• 作用域：仅在定义它的BEGIN...END之间有效，即存储过程、函数中
• 位置：只能放在BEGIN...END中，且只能放在第一句

定义变量，如果不声明默认值，那么初始值为null

DECLARE 变量名 类型 [default值]; 
例如
DECLARE v1 INT DEFAULT 100;

变量赋值

# 方式一：使用=或者:=
SET 局部变量 = 值;
SET 局部变量 := 值;

# 方式二：使用INTO关键字，就相当于在查询语句中插入一段用户变量的声明
SELECT 字段或表达式 INTO 局部变量 FROM 表;
#例如
SELECT AVG(salary) INTO A2 FROM t_employee;

变量的使用

SELECT 局部变量名;

它是怎么在存储过程、函数中进行使用的呢？

DELIMITER $

CREATE PROCEDURE test_var()
BEGIN
	# 变量声明，如果a，b默认值都是0，可以使用DECLARE a,b INT DEFAULT 0来声明
	DECLARE a INT DEFAULT 0;
	DECLARE b INT;
	DECLARE c VARCHAR(25);

	# 变量赋值
	SET a = 1;
	SET b := 2;
	SELECT emp_name INTO c FROM t_emp WHERE emp_id = 101;

	# 变量使用
	SELECT a,b,c;
END $

DELIMITER ;

# 调用存储过程
CALL test_var();

4）用户变量（包括会话用户变量、局部变量）和系统变量的区别

• 声明方面：
  • 用户变量：
    • 会话用户变量的声明和使用需要带一个@
    • 局部变量不需要带@，但是局部变量的定义需要使用关键字DECLAER
  • 系统变量如果使用时带@符号需要带2个
• 定义位置：
  • 会话用户变量可以定义在任何地方
  • 局部变量只能放在BEGIN...END中，且只能放在第一句
• 作用域：
  • 会话用户变量：当前会话
  • 局部变量：定义它的BEGIN...END中

二、定义条件与处理程序

定义条件：指事先定义程序执行过程中可能遇到的问题
处理程序：定义了在遇到问题之后的处理方式，保证了存储过程或者存储函数在遇到警告或者错误时能继续执行，增强了其处理问题的能力，避免程序因异常停止运行

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

1、定义条件

假设我们有一个学生表t_student
它有2个字段，stu_id和stu_name，其中stu_id在声明的时候就加了非空约束

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student`  (
  `stu_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生id',
  `stu_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生姓名'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `t_student` VALUES ('001', 'jack');
INSERT INTO `t_student` VALUES ('002', 'rose');

按照我们的认知，加了非空限制的字段不管是更新还是新增，都不应该支持null做值
但是，我测试的时候发现了一个很神奇的现象
这里的stu_id被我更新成null了！！

后来排查一番发现是我的MySQL数据库缺少STRICT_TRANS_TABLES（严格模式）配置
改完再重启mysql服务就好了

言归正传，我们声明一个存储过程，将t_student中的stu_id更新为null

DELIMITER //
CREATE PROCEDURE testExecption()
	BEGIN
		SET @X = 1;
		UPDATE t_student set stu_id = NULL where stu_name = 'jack';
		SET @X = 2;
		UPDATE t_student set stu_id = '001' where stu_name = 'jack';
		SET @X = 3;
	END //
DELIMITER ;

# 调用存储过程
call testExecption();

# 查询变量X
select @X;

调用存储过程，发现报错如下


这里的1048和23000就是错误码
1048是MySQL_error_code，他是数值类型错误代码
23000是sqlstate_value，它是长度为5的字符串类型错误代码

1）定义条件就是给MySQL中错误码命名。它将一个错误名字和指定的错误条件进行关联
这个名字可以随后被用在定义处理程序的DECALRE HANDLER语句中，

定义条件不是必要的，只有在处理程序中需要用到的时候才要去定义

定义条件使用DECLARE关键字，语法为

DECLARE 错误名称 CONDITION FOR 错误码(或者错误条件);

举例：我们现在要使用Field_Not_Be_Null这个错误名称来与MySQL中违反非空约束的错误类型ERROR 1048 (23000)进行对应

# 方式一：使用MySQL_error_code
DECLARE Field_Not_Be_Null CONDITION FOR 1048;
# 方式二：使用sqlstate_value
DECLARE Field_Not_Be_Null CONDITION FOR SQLSTATE '23000';

2、定义处理程序

1）定义语法

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;

处理方式：

• CONTINUE：遇到指定的错误类型不处理，继续往下执行
• EXIT：遇到指定的错误类型马上退出
• UNDO：遇到指定的错误类型撤回之前的操作，MySQL中暂时不支持

错误类型：

• MySQL_error_code
• sqlstate_value
• 错误名称：上面定义条件里声明的错误名称
• SQLWARNING：匹配所有01开头的sqlstate错误码
• NOT FOUND：匹配所有02开头的sqlstate错误码
• SQLEXCEPTION：匹配所有没被SQLWARNING和NOT FOUND捕获的sqlstate错误码

处理语句：

• 可以是SET 变量 = 值这样的简单语句
• 也可以是BEGIN...END编写的符合语句

接下来，我们看下如何使用上面说的6种错误类型来定义处理程序

# 捕获MySQL_error_code
DECLARE CONTINUE HANDLER FOR 1048 SET @result = 'Column stu_id cannot be null';

# 捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @result = 'Column stu_id cannot be null';

# 先定义条件，再调用错误类型
DECLARE Field_Not_Be_Null CONDITION FOR 1048;
DECLARE CONTINUE HANDLER FOR Field_Not_Be_Null SET @result = 'Column stu_id cannot be null';

# 使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @result = 'Column stu_id cannot be null';

# 使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @result = 'Column stu_id cannot be null';

# 使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @result = 'Column stu_id cannot be null';

2）案例处理
掌握了定义条件与处理程序之后
我们怎么处理上面更新t_student中stu_id为null时执行报错的问题呢

DROP PROCEDURE testExecption;

# 重新定义存储过程，体现错误的处理程序
DELIMITER //
CREATE PROCEDURE testExecption()
	BEGIN
		# 在开始就声明处理程序
		# 方式一：如果是使用错误名称，需要先定义条件
		# DECLARE Field_Not_Be_Null CONDITION FOR 1048;
		# DECLARE CONTINUE HANDLER FOR Field_Not_Be_Null SET @result = 'Column stu_id cannot be null';
		# 方式二：捕获sqlstate_value
		# DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @result = 'Column stu_id cannot be null';
		# 方式三：捕获MySQL_error_code
		DECLARE CONTINUE HANDLER FOR 1048 SET @result = 'Column stu_id cannot be null';
		SET @X = 1;
		UPDATE t_student set stu_id = NULL where stu_name = 'jack';
		SET @X = 2;
		UPDATE t_student set stu_id = '001' where stu_name = 'jack';
		SET @X = 3;
	END //
DELIMITER ;

# 调用存储过程
call testExecption();

# 查询变量X和result
select @X,@result;

执行结果如下

如有错误，欢迎指正！！！