mysql事务-在任何异常时回滚 [英] mysql transaction - roll back on any exception
本文介绍了mysql事务-在任何异常时回滚的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果mysql命令列表上发生任何错误,是否可以自动回滚?
Is it possible to roll back automatically if any error occurs on a list of mysql commands?
例如以下内容:
begin transaction;
insert into myTable values1 ...
insert into myTable values2 ...; -- will throw an error
commit;
现在,在执行时,我希望整个事务失败,因此,我应该不看到myTable中的values1. 但不幸的是,即使交易出错,该表也仍使用values1进行伪造.
now, on execute i want the whole transaction to fail, and therefore i should NOT see values1 in myTable. but unfortunately the table is being pupulated with values1 even though the transaction has errors.
我如何使其回滚的任何想法? (再次出现任何错误)?
any ideas how i make it to roll back? (again, on any error)?
编辑-从DDL更改为标准SQL
EDIT - changed from DDL to standard SQL
推荐答案
您可以使用 13.6.7.2.通过以下方式进行DECLARE ... HANDLER语法:
DELIMITER $$
CREATE PROCEDURE `sp_fail`()
BEGIN
DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
START TRANSACTION;
INSERT INTO `tablea` (`date`) VALUES (NOW());
INSERT INTO `tableb` (`date`) VALUES (NOW());
INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL
IF `_rollback` THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;
有关完整示例,请检查以下 SQL小提琴.
For a complete example, check the following SQL Fiddle.
这篇关于mysql事务-在任何异常时回滚的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文