php - 通过 mysqli 运行多个查询时出现语法错误

Question

我正在尝试将超过 6 小时的行从一个表移动到另一个表。虽然我的代码在 PMA 中完美运行，但我只是通过 PHP 收到错误。这是我的 PHP 代码:

$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$timestamp6h = time() - 21600;
$sql="BEGIN;
INSERT INTO archiv6h SELECT * FROM links WHERE tweettimestamp < $timestamp6h
ON DUPLICATE KEY UPDATE archiv6h.tweetscount= archiv6h.tweetscount+ links.tweetscount, archiv6h.followerscount= archiv6h.followerscount + links.followerscount, archiv6h.tweettimestamp= archiv6h.tweettimestamp + links.tweettimestamp;
DELETE FROM links WHERE tweettimestamp < $timestamp6h;
COMMIT;";
if (!mysqli_query($conn, $sql)) {
    printf("Errormessage: %s\n", mysqli_error($conn));
}
$result = mysqli_query($conn, $sql);
$conn->close();

然后我收到以下错误消息:

错误信息:

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 archiv6h SELECT * FROM links WHERE tweettimestamp < 1521038638 ON ' at line 1

当我将其用作 PMA 中的查询时，它按预期工作:

BEGIN;
INSERT INTO archiv6h SELECT * FROM `links` WHERE tweettimestamp < 1521038638
ON DUPLICATE KEY UPDATE archiv6h.tweetscount= archiv6h.tweetscount+ links.tweetscount, archiv6h.followerscount= archiv6h.followerscount + links.followerscount, archiv6h.tweettimestamp= archiv6h.tweettimestamp + links.tweettimestamp;
DELETE FROM `links` WHERE tweettimestamp < 1521038638;
COMMIT;

有人知道如何在 PHP 中运行查询吗？

Answer 1

BEGIN; 和 COMMIT; 是四个语句。您不能在一个 mysqli_query() 调用中运行多个语句。如果需要事务，请使用 mysqli::begin_transaction()。并且每次 mysqli_query() 调用只使用一个语句:

$conn->begin_transaction();
$conn->query("INSERT ...");
$conn->query("DELETE ...");
$conn->commit();

请注意，您还应该配置 mysqli 以抛出异常。我会按以下方式编写您的脚本:

// set connection variables

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect($servername, $username, $password, $dbname);
$timestamp6h = time() - 21600;

$conn->begin_transaction();
$conn->query("
    INSERT INTO archiv6h SELECT * FROM links WHERE tweettimestamp < $timestamp6h
    ON DUPLICATE KEY UPDATE
        archiv6h.tweetscount    = archiv6h.tweetscount    + links.tweetscount,
        archiv6h.followerscount = archiv6h.followerscount + links.followerscount,
        archiv6h.tweettimestamp = archiv6h.tweettimestamp + links.tweettimestamp
");
$conn->query("DELETE FROM links WHERE tweettimestamp < $timestamp6h");
$conn->commit();

如果有任何失败，$conn->commit(); 将永远不会被执行，脚本将输出一条错误消息。甚至不需要关闭连接，因为它会在脚本结束时关闭。