在 MySQL 中, 特定异常需要特定处理. 这些异常可以联系到错误, 以及子程序中的一般流程控制. 定义异常是事先定义程序执行过程中遇到的问题, 异常处理定义了在遇到问题时对应当采取的处理方式, 并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行.
1 异常定义
1.1 语法
DECLARE condition_name CONDITION FOR [condition_type];
1.2 说明
condition_name 参数表示异常的名称;
condition_type 参数表示条件的类型, condition_type 由 SQLSTATE [VALUE] sqlstate_value|mysql_error_code 组成:
sqlstate_value 和 mysql_error_code 都可以表示 MySQL 的错误;
sqlstate_value 为长度为 5 的字符串类型的错误代码;
mysql_error_code 为数值类型错误代码;
1.3 示例
定义 "ERROR 1148(42000)" 错误, 名称为 command_not_allowed. 可以有以下两种方法:
方法一: 使用 sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000;
方法二: 使用 mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
2 自定义异常处理
2.1 异常处理语法
DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement
2.2 参数说明
handler_type: CONTINUE|EXIT|UNDO
handler_type 为错误处理方式, 参数为 3 个值之一;
CONTINUE 表示遇到错误不处理, 继续执行;
EXIT 表示遇到错误时马上退出;
UNDO 表示遇到错误后撤回之前的操作, MySQL 暂不支持回滚操作;
condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
condition_value 表示错误类型;
SQLSTATE [VALUE] sqlstate_value 为包含 5 个字符的字符串错误值;
condition_name 表示 DECLARE CONDITION 定义的错误条件名称;
SQLWARNING 匹配所有以 01 开头的 SQLSTATE 错误代码;
NOT FOUND 匹配所有以 02 开头的 SQLSTATE 错误代码;
SQLEXCEPTION 匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码;
mysql_error_code 匹配数值类型错误代码;
2.3 异常捕获方法
方法一: 捕获 sqlstate_value 异常
这种方法是捕获 sqlstate_value 值. 如果遇到 sqlstate_value 值为 "42S02, 执行 CONTINUE 操作, 并输出"NO_SUCH_TABLE" 信息
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02 SET @info='NO_SUCH_TABLE';
方法二: 捕获 mysql_error_code 异常
这种方法是捕获 mysql_error_code 值. 如果遇到 mysql_error_code 值为 1146, 执行 CONTINUE 操作, 并输出 "NO_SUCH_TABLE" 信息;
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
方法三: 先定义条件, 然后捕获异常
- DECLARE no_such_table CONDITION FOR 1146;
- DECLARE CONTINUE HANDLER FOR NO_SUCH_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';
3 综合示例
创建一个表, 设置该表的主键, 在不定义异常处理和定义异常处理情况下看执行到哪一步.
- show databases;use wms;
- create table location
- (
- location_id int primary key,
- location_name varchar(50)
- );
示例 1: 不定义异常情况下
- DELIMITER //CREATE PROCEDURE handlerInsertNoException()BEGIN
- /*DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;*/
- SET @x=1;
- INSERT INTO location VALUES (1,'Beijing');
- SET @x=2;
- INSERT INTO location VALUES (1,'Wuxi');
- SET @x=3;END;
- //
- DELIMITER ;
调用存储过程与结果:
- mysql> call handlerInsertNoException();
- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> select @x;
- +------+| @x |+------+| 2 |+------+1 row in set (0.00 sec)
- mysql> select * from location;
- +-------------+---------------+| location_id | location_name |
- +-------------+---------------+
- | 1 | Beijing |+-------------+---------------+1 row in set (0.00 sec)
注意: 操作示例 2 前要清空表中数据, 并退出重新登录, 以免客户端变量 @x 影响, 详细说明参见结论中的第一点.
- mysql> truncate table location;
- Query OK, 0 rows affected (0.04 sec)
- mysql> select * from location;Empty set (0.00 sec)
- mysql> exit;
- Bye
- david@Louis:~$ mysql -u root -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 53Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)
- mysql> use wms;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> select * from location;Empty set (0.00 sec)
- mysql> select @x;
- +------+
- | @x |
- +------+
- | NULL |
- +------+1 row in set (0.00 sec)
示例 2: 定义异常处理情况下:
- DELIMITER //CREATE PROCEDURE handlerInsertWithException()BEGIN
- DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1; SET @x=1; INSERT INTO location VALUES (1,'Beijing'); SET @x=2; INSERT INTO location VALUES (1,'Wuxi'); SET @x=3;END;
- //
- DELIMITER ;
调用存储过程与结果:
- mysql> CALL handlerInsertWithException();
- Query OK, 0 rows affected (0.09 sec)
- mysql> select @x;
- +------+| @x |+------+| 3 |+------+1 row in set (0.00 sec)
说明与结论:
一, MySQL 中,@var_name 表示用户变量, 使用 SET 语句为其赋值, 用户变量与连接有关, 一个客户端定义的变量不能被其他客户端看到或者使用. 当客户端退出时, 该客户端连接的所有变量将自动释放.
二, 在示例 1 中, 由于注释了异常的声明 "", 此时向表中插入相同主键, 就会触发异常, 并且采取默认 (EXIT) 路径; 且查看此时的 @x 返回 2, 表示下面的 INSERT 语句并没有执行就退出了.
三, 定义了异常处理, 此时遇到错误也会按照异常定义那样继续执行; 但只有第一条数据被插入到表中, 此时用户变量 @x=3 说明已经执行到了结尾;
来源: http://database.51cto.com/art/201807/578512.htm