MySql存储过程捕获异常回滚

DROP PROCEDURE IF EXISTS pro_test;
CREATE PROCEDURE pro_test
(
    para_a varchar(50),
    para_b varchar(50)
)
BEGIN
    DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值0
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置result_code为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code = 2; -- 如果表中没有下一条数据则置为2

    START TRANSACTION; -- 开始事务
    /*
    * 这里写具体的业务处理...
    * 业务处理过程中可以根据实际情况自定义result_code
    */
    IF result_code = 1 THEN -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
        ROLLBACK; 
    ELSE 
        COMMIT; 
    END IF;
SELECT result_code;
END

代码如上所示

这里补充一点作者也是刚刚才了解到的一点知识：

如果事务中所有sql语句执行正确则需要自己手动提交commit；否则有任何一条执行错误，需要自己提交一条rollback，这时会回滚所有操作，否则在出错的sql语句前面正确的sql语句会在下次执行了一条commit 或begin或start transaction（新开一个事务会将该链接中的其他未提交的事务提交，相当于commit！）时提交运行。

具体看代码：

DROP PROCEDURE IF EXISTS `PRO2`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `PRO2`()
BEGIN
    DECLARE t_error INTEGER default 0;
    DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

    START TRANSACTION;
        INSERT INTO test VALUES    (1, '2'); #1
        INSERT INTO test VALUES    (1, '3'); #2
        IF t_error = 1 THEN
            #ROLLBACK; #3
        ELSE
            COMMIT;
        END IF;
END;

如上面的代码，#1和#2由于主键冲突，所以会报错。

如果这时#3的ROLLBACK语句被注释掉，然后把#1和#2换为：

INSERT INTO test VALUES    (2, '2');
INSERT INTO test VALUES    (3, '3');

再运行一次，你会发现test表里除了id=2，id=3的行外，还有一条id=1的记录。

这就是遇到错误没有回滚，已经正确执行的sql语句会在下次commit时被再次提交。

所以，上面的#3里的ROLLBACK一定要注意加上。

好吧，虽然知道大家都对ROLLBACK印象深刻，不会犯这种错误，但作者觉得 已经正确执行的sql语句在遇到错误后，会在下次遇到commit时被再次提交  ，这个点作者也忽略了，所以自己记录一下。

看到的童鞋不喜勿喷哈！

参考：

MySql存储过程捕获异常回滚

对mysql事务提交、回滚的错误理解