MySQL 事务难题

Question

我需要在单个原子事务中执行多次插入。例如：

开始交易；

插入...

插入...

提交；

然而，当 MySQL 遇到错误时，它只会中止导致错误的特定语句。例如，如果第二个插入语句中有错误，提交仍然会发生，并且第一个插入语句将被记录。因此，当发生错误时，MySQL 事务并不是真正的事务。为了解决这个问题，我使用了一个错误退出处理程序来回滚事务。现在交易已悄然中止，但我不知道问题出在哪里。

所以你面临的难题是：

如何让 MySQL 在遇到错误时中止事务，并将错误代码传递给调用者？

Answer 1

如何让 MySQL 在遇到错误时中止事务，并将错误代码传递给调用者？

MySQL 确实会将错误代码传递给调用者，并且根据此错误代码，调用者可以自由决定是否要提交目前完成的工作（忽略此特定 INSERT< 的错误） /code> 语句）或回滚事务。

这与 PostgreSQL 不同，后者总是在出错时中止事务，这种行为是许多问题的根源。

更新：

在存储过程中使用无条件ROLLBACK是一种不好的做法。

存储过程是可堆栈的，而事务则不然，因此嵌套存储过程中的ROLLBACK 将回滚到事务的最开始位置，而不是回滚到存储过程执行的状态。

如果要使用事务在错误时恢复数据库状态，请使用 SAVEPOINT 构造和 DECLARE HANDLER 回滚到保存点：

CREATE PROCEDURE prc_work()
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
        SAVEPOINT sp_prc_work;
        INSERT  …;
        INSERT  …;
        …
END;

任一插入失败都将回滚所做的所有更改按该程序并退出。

Answer 2

使用Quassnoi先生的例子，这是我捕捉特定错误的最佳方法：

这个想法是使用tvariable来捕捉简单的错误消息，然后你可以捕捉你认为可能发生的sql状态，将自定义消息保存到你的变量中：

DELIMITER $

DROP PROCEDURE IF EXISTS prc_work $
CREATE PROCEDURE prc_work ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '23000'
  BEGIN
    SET @prc_work_error = 'Repeated key';
    ROLLBACK TO sp_prc_work;
  END;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @prc_work_error = 'Unknown error';
    ROLLBACK TO sp_prc_work;
  END;

  START TRANSACTION;
    SAVEPOINT sp_prc_work;
    INSERT into test (id, name) VALUES (1, 'SomeText');
  COMMIT;
END $

DELIMITER ;

然后你只需进行通常的调用，并对变量执行 select 语句，例如：

call prc_work(); select @prc_work_error;

如果没有错误，则返回 NULL；如果出现错误，则返回消息错误。如果您需要持久的错误消息，您可以选择创建一个表来存储它。

它很乏味而且不太灵活，因为需要为您想要捕获的每个状态代码提供一个 DECLARE EXIT HANDLER 段，它也不会显示详细的错误消息，但是嘿，它可以工作。