mysql 事务 - 回滚任何异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19905900/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql transaction - roll back on any exception
提问by Urbanleg
Is it possible to roll back automatically if any error occurs on a list of mysql commands?
如果 mysql 命令列表发生任何错误，是否可以自动回滚？
for example something along the lines of:
例如：
begin transaction;
insert into myTable values1 ...
insert into myTable values2 ...; -- will throw an error
commit;
now, on execute i want the whole transaction to fail, and therefore i should NOTsee values1 in myTable. but unfortunately the table is being pupulated with values1 even though the transaction has errors.
现在，在执行我希望整个交易失败，因此我应该不看到值1在myTable的。但不幸的是，即使交易有错误，该表也会使用 values1。
any ideas how i make it to roll back? (again, on any error)?
任何想法我如何让它回滚？（再次，任何错误）？
EDIT - changed from DDL to standard SQL
编辑 - 从 DDL 更改为标准 SQL
回答by wchiquito
You can use 13.6.7.2. DECLARE ... HANDLER Syntaxin the following way:
您可以使用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 ;
For a complete example, check the following SQL Fiddle.
有关完整示例，请查看以下SQL Fiddle。
回答by KGs
You could use EXIT HANDLER if you for example need to SIGNAL a specific SQL EXCEPTION in your code. For instance:
例如，如果您需要在代码中发出特定的 SQL EXCEPTION 信号，则可以使用 EXIT HANDLER。例如：
DELIMITER $$
CREATE PROCEDURE `sp_fail`()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK; -- rollback any changes made in the transaction
RESIGNAL; -- raise again the sql exception to the caller
END;
START TRANSACTION;
insert into myTable values1 ...
IF fail_condition_meet THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error detected.', MYSQL_ERRNO = 2000;
END IF;
insert into myTable values2 ... -- this will not be executed
COMMIT; -- this will not be executed
END$$
DELIMITER ;
回答by James
The above solution are good but to make it even simpler
上面的解决方案很好，但让它更简单
DELIMITER $$
CREATE PROCEDURE `sp_fail`()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK; -- rollback any error in the transaction
END;
START TRANSACTION;
insert into myTable values1 ...
insert into myTable values2 ... -- Fails
COMMIT; -- this will not be executed
END$$
DELIMITER ;
