MySQL数据库，详解异常捕获及处理(一)

需求背景

我们在写存储过程的时候，可能会出现下列⼀些情况：

1. 插⼊的数据违反唯⼀约束，导致插⼊失败

2. 插⼊或者更新数据超过字段最⼤长度，导致操作失败3. update影响⾏数和期望结果不⼀致遇到上⾯各种异常情况的时，可能需要我们能够捕获，然后可能需要回滚当前事务。

本⽂主要围绕异常处理这块做详细的介绍。

此时我们需要使⽤游标，通过游标的⽅式来遍历select查询的结果集，然后对每⾏数据进⾏处理。

准备数据

创建库：javacode2018

创建表：test1，test1表中的a字段为主键。

/*建库javacode2018*/

drop database if exists javacode2018;

create database javacode2018;

/*切换到javacode2018库*/

use javacode2018;

DROP TABLE IF EXISTS test1;

CREATE TABLE test1(a int PRIMARY KEY);异常分类

我们将异常分为mysql内部异常和外部异常

mysql内部异常

当我们执⾏⼀些sql的时候，可能违反了mysql的⼀些约束，导致mysql内部报错，如插⼊

数据违反唯⼀约束，更新数据超时等，此时异常是由mysql内部抛出的，我们将这些由

mysql抛出的异常统称为内部异常。

外部异常

当我们执⾏⼀个update的时候，可能我们期望影响1⾏，但是实际上影响的不是1⾏数

据，这种情况：sql的执⾏结果和期望的结果不⼀致，这种情况也我们也把他作为外部异

常处理，我们将sql执⾏结果和期望结果不⼀致的情况统称为外部异常。

Mysql内部异常

示例1

test1表中的a字段为主键，我们向test1表同时插⼊2条数据，并且放在⼀个事务

中执⾏，最终要么都插⼊成功，要么都失败。

创建存储过程：

/*删除存储过程*/

DROP PROCEDURE IF EXISTS proc1;

/*声明结束符为$*/

DELIMITER $

/*创建存储过程*/

CREATE PROCEDURE proc1(a1 int,a2 int)

BEGIN

START TRANSACTION;

INSERT INTO test1(a) VALUES (a1);

INSERT INTO test1(a) VALUES (a2);

COMMIT;

END $/*结束符置为;*/

DELIMITER ;

上⾯存储过程插⼊了两条数据，a的值都是1。

验证结果：

mysql> DELETE FROM test1;

Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc1(1,1);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> SELECT * from test1;

+---+

| a |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

上⾯先删除了test1表中的数据，然后调⽤存储过程proc1，由于test1表中的a字

段是主键，插⼊第⼆条数据时违反了a字段的主键约束，mysql内部抛出了异

常，导致第⼆条数据插⼊失败，最终只有第⼀条数据插⼊成功了。

上⾯的结果和我们期望的不⼀致，我们希望要么都插⼊成功，要么失败。

那我们怎么做呢？我们需要捕获上⾯的主键约束异常，然后发现有异常的时候执⾏

rollback回滚操作，改进上⾯的代码，看下⾯⽰例2。

示例2

我们对上⾯⽰例进⾏改进，捕获上⾯主键约束异常，然后进⾏回滚处理，如下：

创建存储过程：

/*删除存储过程*/

DROP PROCEDURE IF EXISTS proc2;

/*声明结束符为$*/

DELIMITER $

/*创建存储过程*/

CREATE PROCEDURE proc2(a1 int,a2 int)

BEGIN /*声明⼀个变量，标识是否有sql异常*/

DECLARE hasSqlError int DEFAULT FALSE;

/*在执⾏过程中出任何异常设置hasSqlError为TRUE*/

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

/*开启事务*/

START TRANSACTION;

INSERT INTO test1(a) VALUES (a1);

INSERT INTO test1(a) VALUES (a2);

/*根据hasSqlError判断是否有异常，做回滚和提交操作*/

IF hasSqlError THEN

ROLLBACK;

ELSE

COMMIT;

END IF;

END $

/*结束符置为;*/

DELIMITER ;

上⾯重点是这句：

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

当有sql异常的时候，会将变量hasSqlError的值置为TRUE。

模拟异常情况：

mysql> DELETE FROM test1;

Query OK, 2 rows affected (0.00 sec)

mysql> CALL proc2(1,1);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from test1;

Empty set (0.00 sec)

上⾯插⼊了2条⼀样的数据，插⼊失败，可以看到上⾯test1表⽆数据，和期望

结果⼀致，插⼊被回滚了。模拟正常情况：

mysql> DELETE FROM test1;

Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc2(1,2);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from test1;

+---+

| a |

+---+

| 1 |

| 2 |

+---+

2 rows in set (0.00 sec)

上⾯插⼊了2条不同的数据，最终插⼊成功。