MySQL 定义条件与处理程序 的详细讲解

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. 最后:

限于自身水平，其中存在的错误，希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 ！！！

---