我在 SQL 事务中收到 Uncaught PDO Exepction 错误

Question

这段代码：

$sql = '

    START TRANSACTION;

    INSERT INTO translation (lang, author, title, text)

      VALUES(:lang, :author, :title, :text);

    INSERT INTO article (translation, author, category, views, banner, visible) 

      VALUES(LAST_INSERT_ID(), :author, :category, 0, :banner, :visible);

    COMMIT;';

    $params = array("lang" => $lang, 

                    "author" => $author, 

                    "title" => $title, 

                    "text" => $content, 

                    "category" => $category, 

                    "banner" => $banner, 

                    "visible" => $v);

    $stmt = $conn->prepare($sql);

    $stmt->execute($params);

给我这个错误：

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO translation (lang, author, title, text) VALUES(?, ?, ?, ?); ' at line 2 in C:\xampp\htdocs\admin\add-article.php:57 Stack trace: #0 C:\xampp\htdocs\admin\add-article.php(57): PDO->prepare('\r\n START TRA...') #1 {main} thrown in C:\xampp\htdocs\admin\add-article.php on line 57

这是我第一次尝试使用 PDO 事务。我直接在 phpMyAdmin 中尝试了这个 sql

BEGIN;

    INSERT INTO translation (lang, author, title, text)

      VALUES(1, 2, "test", "test");

    INSERT INTO article (translation, author, category, views, banner, visible) 

      VALUES(LAST_INSERT_ID(), 2, 1, 100, "", 1);

COMMIT;

它有效，但在 php 脚本中无效。我根据这篇文章写了“开始交易” 有什么想法吗？

Answer 1

您需要单独运行每个查询。此外，通常事务包含在 try catch 中。 try {    $pdo->beginTransaction();    $sql = 'INSERT INTO translation (lang, author, title, text)        VALUES(:lang, :author, :title, :text)';    $params = array("lang" => $lang,                     "author" => $author,                     "title" => $title,                     "text" => $content,     );    $stmt = $conn->prepare($sql);    $stmt->execute($params);        $sql = 'INSERT INTO article (translation, author, category, views, banner, visible)         VALUES(LAST_INSERT_ID(), :author, :category, 0, :banner, :visible)';    $params = array(                    "author" => $author,                     "category" => $category,                     "banner" => $banner,                     "visible" => $v    );    $stmt = $conn->prepare($sql);    $stmt->execute($params);        $pdo->commit();}catch (Exception $e){    $pdo->rollback();    throw $e;}