MySQL 定义条件与处理程序 的详细讲解
每博一文案
雨了再送伞,就有些多余了” #情感# #治愈# #国漫# 一禅小和尚的微博视频
很多关系中的失望往往来源于刚刚在一起的时候,彼此都是最好的样子,相处越久,
发现身上有越多自己无法接受的东西。一方想要改变,另一方逃避改变,久而久之,
一拍两散。其实除非一个人自己想要改变,否则你永远无法叫醒一个装睡的人。
爱一个人可以把一切不合适都磨合成相爱,而试图控制并改变一个人,可以把爱都变成
强硬的伤害,就像手中紧握的流沙,握的越紧流失得越快。
作家马德写过一句话,我慢慢明白了为什么我不快乐,因为我总是期待一个结果,
看一本书,期待它让我,变得深刻。跑一会步,期待它让我瘦下来,发一条微信,期待他被回复,
对别人好,期待被回代已好,这些预设的期待如果实现了,我长舒一口气,如果没有实现呢,就自怨自艾,
过低的期待,你会感觉不到期待关系的好处。过高的期待,你会总是把自己弄到失望和愤怒的情绪里,
一段感情中最大的遗憾就是总想改变对方,却拒绝看清自己。
因此,不要试图通过改变他人来活得快乐,不要把期待,都放在别人身上,与其被强求不来的期待所困,
倒不如改变自己,活出自己的颜色。爱,不是改变对方,而是一起成长。
原我们能够成为自己想要的模样,不轻易为喜欢的人改变自己,更不去尝试改变他人。
—————— 一禅心灵庙语
1. 定义条件于处理程序的概念
定义条件
是事先 定义程序执行过程中可能遇到的问题,处理程序定义了在遇到问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行。其实就是和Java中的异常处理一样的,当在存储过程或存储函数中出现了,捕获异常,处理异常
说明:定义条件和处理程序在 存储过程
和 存储函数
中都是使用的。
2. 案例分析
案例分析: 错误演示:创建一个名称为“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 = 'Abel'
WHERE last_name = 'Abel';
SET @x = 3;
END $
DELIMITER ;
-- 调用该存储过程
CALL UpdateDataNoCondition();
报错:email 存在 not null 非空约束,不可赋值为 null;
SELECT @x;
从查看 @x
会话用户变量的值 为 1 ,再结合创建存储过程中的 sql语句代码中可以得出:在存储过程中未定义条件和处理程序,且当存储过程中执行的 sql 语句报错时,MySQL 数据库会抛出错误,并退出当前的sql语句。不再向下继续执行。
从报错结果上看:给了我们 1048 ( 23000 )
的错误代码。这里记一下。后面我们会用上。
3. 定义条件
定义条件
就是根据 MySQL中给的错误码,自己对错误码进行一个重新命名。因为MySQL给我们的错误码一般都是 数值或数值字符串 ,不利于我们阅读理解,而我们自己根据错误码进行一个别名称,这有助于我们我们更好的理解。它将一个错误名字和 指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER
语句中。
定义条件使用 DECLARE
语句,语法格式如下:
DECLARE 错误名称 CONDITION FOR 错误码 (或错误条件);
错误码的说明:
MySQL_error_code
和 sqlstate_value
都可以表示 MySQL的错误
MySQL_error_code
表示的是数值类型错误代码sqlstate_value
表示的是长度为 5 的字符串类型错误代码
例如上图,我们刚刚的错误演示,报错提示
1048
是 数值类型的错误码,属于 MySQL_error_code
(23000)
是字符串类型的错误码,属于sqlstate_value
注意: 默认是MySQL_error_code 数值类型的错误码。因为默认是MySQL_error_code 数值类型的错误码,当我们使用的是字符串类型的错误码时,必须指明 SQLSTATE 字符串类型错误码;为了防止数值字符串被转化为了数值类型错误码。
举例1: 定义“Field_Not_Be_NULL”的错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对 应。
-- 方式一:根据数值类型错误码,命名
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
-- 默认是MySQL_error_code 数值类型的错误码;
-- 方式二:根据字符串类型错误码,命名,使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
/* 因为默认是MySQL_error_code 数值类型的错误码,为了防止数值字符串被转化为了数值类型错误码
所以必须指明 SQLSTATE 字符串类型错误码;
*/
举例2: 定义"ERROR 1148(42000)"错误,名称为command_not_allowed。
-- 使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
-- 使用sqlstate_value 字符串错误码必须指明为 sqlstate
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
4. 定义处理程序
可以为 SQL 执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时,使用 DECLARE 语句的语法如下:
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;
-
处理方式 :处理方式有
3
个取值:CONTINUE,EXIT,UNDO
CONTINUE
: 表示遇到错误不处理,继续执行EXIT
: 表示遇到错误马上退出UNDO
:表示遇到错误后,撤回之前的操作。MySQL中暂时不支持这样的操作。
-
错误类型 :(即条件) 可以有如下取值:
SQLSTATE
:字符串错误码,表示长度为 5 的sqlstate_value
类型的错误代码MySQL_error_code
:匹配数值类型错误代码SQLWARNING
:表示匹配所有以 01 开头的数值SQLSTATE错误码NOT FOUND
: 表示匹配所有以 02 开头的数值SQLSTATE错误码SQLEXCEPTION
:表示匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE的错误代码
-
处理语句: 如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句。
处理语句可以是像 “SET 变量 = 变量值
”这样简单语句,也可以是使用 BEGIN...END
编写的复合语句
定义处理程序的几种方式,代码如下:
方法1:捕获sqlstate_value
-- 方法1:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info = 'NO_SUCH_TABLE';
/*错误类型:捕获到该字符串 42S02 错误码
错误处理方式: 进行 continue 继续向下执行程序
处理语句: 进行一个简单的赋值 @info = 'NO_SUCH_TABLE' 操作*/
方法2:捕获mysql_error_value
-- 方法2:捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146
SET @info = 'NO_SUCH_TABLE';
/*错误类型:捕获到该默认数值类型 1146 mysql_error_value
错误处理方式: 进行 continue 继续向下执行程序
处理语句: 进行一个简单的赋值 @info = 'NO_SUCH_TABLE' */
方法3,先根据错误码自己定义条件,再根据自己命名的错误码处理错误
-- 方法3,先根据错误码自己定义条件,再根据自己命名的错误码处理错误
DECLARE no_such_table CONDITION FOR 1146; -- 默认是数值类型错误码
DECLARE CONTINUE HANDLER FOR no_such_table
SET @info = 'NO_SUCH_TABLE';
/*先根据数值类型错误码 1146 自己定义错误码别称
错误类型:捕获到定义的错误码别称,
处理方式:continue 继续向下执行程序
处理语句:简单的赋值操作:SET @info = 'NO_SUCH_TABLE';
*/
方法4: 使用SQLWARNING
-- 方法4: 使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING
SET @info = 'NO_SUCH_TABLE';
/* 错误类型:捕获到 SQLWARNING 表示匹配所有以 01 开头的数值 SQLSTATE错误码
处理方式:exit 捕获到该错误,直接停止程序向下执行
处理语句: 简单的赋值操作:SET @info = 'NO_SUCH_TABLE';
*/
方法5: 使用NOT FOUND
-- 方法5: 使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND
SET @info = 'NO_SUCH_TABLE';
/* 错误类型:捕获到 NOT FOUND 表示:匹配所有以02开头的SQLSTATE错误代码
处理方式: exit 捕获到该错误码,直接停止程序向下执行
处理语句: 简单的赋值操作: SET @info = 'NO_SUCH_TABLE';
*/
**方法6: 使用SQLEXCEPTION **
-- 方法6: 使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET @info = 'ERROR';
/* 错误类型: 捕获到 for sqlexception 表示匹配所有没有被 SQLWARNING** 或 NOT FOUND 捕获的 SQLSTATE的错误代码
处理方式: exit 捕获到该错误码,直接停止程序下去
处理语句: 简单的赋值操作: SET @info = 'ERROR';
*/
举例: 在存储过程UpdateDataNoCondition()中,定义处理程序,捕获 sqlstate_value
值,当遇到MySQL_error_code
值为1048时,执行CONTINUE
操作,并且将@proc_value
的值设置为-1。
就是一开始我们的错误演示的是同一个。现在我们进行一个定义处理程序,当捕获到该 not null 非空约束错误码,进行一个 CONTINUE
操作处理,继续向下执行。
DELIMITER $
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
-- 声明处理程序
-- 方式1 捕获数值类型的错误码,默认是MySQL_error_code数值类型错误码
DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
-- 方式2 捕获字符串类型的错误码,SQLSTATE
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @prc_value = -2;
SET @x = 1;
UPDATE employees
SET email = NULL
WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees
SET email = 'aabbel'
WHERE last_name = 'Abel';
SET @x = 3;
END $
DELIMITER ;
-- 调用存储过程
CALL UpdateDataNoCondition();
SELECT @x,@prc_value;
我们发现经过我们的捕获该错误,并处理了该错误,程序不会报错了。直接执行到底。
5. 案例解决
创建一个名称为“InsertDataWithCondition
”的存储过程,代码如下。 在存储过程中,定义处理程序,捕获sqlstate_value
值,当遇到sqlstate_value
值为23000
时,执行EXIT操 作,并且将@proc_value
的值设置为-1。
-- 准备工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);
DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
-- 处理程序
-- 方式一:默认查询数值错误码
DECLARE EXIT HANDLER FOR 1062 SET @pro_value = -1;
-- 方式二 sqlstate 字符串数值类型错误码
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @pro_value = -1;
-- 方式三,先根据错误码自行定义错误别称,再根据错误别称处理
/* DECLARE duplicate_entry CONDITION FOR 1062;
declare exit handler for duplicate_entry set @pro_value = -1;
*/
SET @x = 1;
INSERT INTO departments(department_name)
VALUES ('测试');
SET @x = 2;
INSERT INTO departments(department_name)
VALUES ('测试'); -- department_name 含有唯一约束
SET @x = 3;
END //
DELIMITER ;
-- 调用存储过程
CALL InsertDataWithCondition();
SELECT @x,@pro_value;
6. 最后:
限于自身水平,其中存在的错误,希望大家给予指教,韩信点兵——多多益善,谢谢大家,后会有期,江湖再见 !!!