mysql 事务 - 回滚任何异常

2021-11-20 00:00:00 transactions mysql rollback

如果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)?

EDIT - 从 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 Fiddle.

For a complete example, check the following SQL Fiddle.

相关文章