php - 如何将此 SQL 查询无错误地转换为 PDO 事务

Question

我有这个 SQL 查询:

BEGIN;
INSERT INTO users (username, password)
  VALUES('alpha', 'omega');
INSERT INTO profiles (userid, cv, website) 
  VALUES(LAST_INSERT_ID(),'some cv things', 'www.domain.com');
COMMIT;

我想使用 PDO 而不是 MYSQL Transcation 这样我就可以提取 catched error 因为我没有从MYSQL 事务，我试过的是

$dbh->beginTransaction();
try {
 $stmt = $dbh->prepare("
  INSERT INTO users (username, password)
    VALUES('alpha', 'omega');
  INSERT INTO profiles (userid, cv, website) 
    VALUES(LAST_INSERT_ID(),'some cv things', 'www.domain.com');
 ");
 $stmt->execute();
 $dbh->commit();
} catch (PDOException $e) {
 $dbh->rollback();
 throw $e;
}

但我一直收到这个错误

Uncaught PDOException: There is no active transaction . . . PDO->rollBack() #1 {main} thrown

记录已INSERT插入表中，但错误也一直显示。

我已经尝试使用 $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE); 但仍然收到错误消息。

然后尝试删除 try-catch 并使用 PDO Transactions 保留 Query 并得到此错误消息

Uncaught PDOException: There is no active transaction . . . PDO->commit() #1 {main} thrown

Answer 1

您在错误处理程序中抛出异常:

catch (PDOException $e) {
 $dbh->rollback();
 throw $e;  // <-- right here
}

这将导致异常一直持续到它被处理为止，并且if it's not handled it will throw a fatal error :

When an exception is thrown, code following the statement will not be executed, and PHP will attempt to find the first matching catch block. If an exception is not caught, a PHP Fatal Error will be issued with an "Uncaught Exception ..." message, unless a handler has been defined with set_exception_handler().

如果您的异常处理程序正在完全处理异常，您应该删除该行。