先科普一点知识:mysql在一个事物中发生错误时,是不会回滚整个事物的。
我们先证明这点
mysql> desc wzy;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | NO | PRI | NULL | |
| y | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.09 sec)
这是表结构
我们建一个procedure用于测试
CREATE DEFINER=`root`@`%` PROCEDURE `err_test1`(in v_x int,in v_y int,in v_z int)
SQL SECURITY INVOKER
BEGIN
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
COMMIT;
END
mysql> call wzy.err_test1(1,2,3);
Query OK, 0 rows affected (0.09 sec)
mysql> select * from wzy;
+---+------+
| x | y |
+---+------+
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
+---+------+
3 rows in set (0.00 sec)
mysql> truncate table wzy;
Query OK, 0 rows affected (0.12 sec)mysql> call wzy.err_test1(1,1,3);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from wzy;
+---+------+
| x | y |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)
先测试过程可用,再清空表
mysql> call wzy.err_test1(1,1,3);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from wzy;
+---+------+
| x | y |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)
这次第二行数据与第一行主键冲突,过程结束,第三行数据并没有进入数据库,但是第一行数据还是进入了数据库
现在我们看下
DECLARE EXIT HANDLER FOR SQLEXCEPTION
和
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
两种方式处理的procedure,在错误时处理的不同。
DECLARE EXIT HANDLER FOR SQLEXCEPTION的部分
SQL SECURITY INVOKER
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
COMMIT;
END
清空表,跑包
mysql> truncate table wzy;
Query OK, 0 rows affected (0.06 sec)
mysql> call err_test2(1,1,3);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from wzy;
+---+------+
| x | y |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)
我们发现只有第一行数据入库,与不声明exit情况相同
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION的部分
CREATE DEFINER=`root`@`%` PROCEDURE `err_test3`(in v_x int,in v_y int,in v_z int)
SQL SECURITY INVOKER
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
COMMIT;
END
清空,并执行
mysql> truncate table wzy;
Query OK, 0 rows affected (0.07 sec)
mysql> call err_test3(1,1,3);
Query OK, 0 rows affected (0.02 sec)
mysql> select * from wzy;
+---+------+
| x | y |
+---+------+
| 1 | 2 |
| 3 | 5 |
+---+------+
2 rows in set (0.00 sec)
我们看到1、3都被执行了。可见continue会忽略报错继续执行。
那我们该如何回滚整个包的事物呢?我们可以通过定义一个变量来判断是否进行回滚。
这里再对比下EXIT与CONTINUE的区别
EXCEPTION 的情况下用变量做判断。
CREATE DEFINER=`root`@`%` PROCEDURE `err_test4`(in v_x int,in v_y int,in v_z int)
SQL SECURITY INVOKER
BEGIN
DECLARE EXCEPTION INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET EXCEPTION = 1;
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
IF EXCEPTION = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END
mysql> truncate table wzy;
Query OK, 0 rows affected (0.13 sec)
mysql> select * from wzy;
Empty set (0.00 sec)
mysql> call err_test4(1,1,3);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wzy;
+---+------+
| x | y |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)
数据还是入库了!可见变量没有发挥作用。
CONTINUE 的情况
CREATE DEFINER=`root`@`%` PROCEDURE `err_test5`(in v_x int,in v_y int,in v_z int)
SQL SECURITY INVOKER
BEGIN
DECLARE EXCEPTION INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET EXCEPTION = 1;
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
IF EXCEPTION = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END
mysql> truncate table wzy;
Query OK, 0 rows affected (0.24 sec)
mysql> call err_test5(1,1,3);
Query OK, 0 rows affected (0.15 sec)
mysql> select * from wzy;
Empty set (0.00 sec)
整个procedure都被回滚了!可见CONTINUE会执行到最后,变量发挥了作用
经过对比我们可以知道
EXIT会在执行错误时,直接跳出过程,不去执行剩下的语句,不利与判断回滚,而且不声明时,mysql默认就是这么处理的。
CONTINUE会在执行错误时,继续执行过程,我们可以通过设置变量判断这个过程是否需要回滚