多个查询相互依赖 [英] Multiple queries dependent on each other

问题描述

我有两个相互依赖的查询，即如果第一个查询未执行，则第二个查询不应执行，反之，如果第二个查询无法执行，则第一个查询不应执行.

I have two queries both dependent on each other, i.e. if first query is not executed the second shouldn't be executed, and the other way around if second can't be executed the first shouldn't be executed.

INSERT INTO `table` VALUES (1,2,3)
UPDATE `otherTable` SET `val1`=1 WHERE `id`=$idOfInsert

ON DUPLICATE KEY UPDATE 不是答案.

我尝试使用 mysqli::multi_query 但结果它执行第一个，即使第二个无法执行(它会因错误而停止).

I tried using mysqli::multi_query but as it turned out it executes the first even though the second can't be executed (it stops on error).

如何在 PHP 中实现这一点?

How can I achieve this in PHP?

推荐答案

我正在寻找的是事务.@tmuguet 的回答当时确实对我有帮助，但现在从时间的角度来看，我想提供更新的答案.

What I was looking for are transactions. @tmuguet's answer has really helped me back then, but now looking at it from the perspective of time I would like to provide a more up to date answer.

每个查询都需要单独执行，并使用准备好的语句来防止 SQL 注入.

Each query needs to be executed separately and using prepared statements to prevent SQL injection.

try {
    // Start transaction
    $mysqli->begin_transaction();

    $mysqli->query('INSERT INTO `table` VALUES (1,2,3)');

    $stmt = $mysqli->prepare('UPDATE otherTable SET val1=1 WHERE id=?');
    $stmt->bind_param('s', $idOfInsert);
    $stmt->execute();

    // Commit changes
    $mysqli->commit();
} catch (\Throwable $e) {
    // Something went wrong. Rollback
    $mysqli->rollback();
    throw $e;
}

当然要使其正常工作，您需要启用 mysqli 错误报告.只需在代码中的 new mysqli() 之前添加这一行.

Of course for this to work properly you need to have mysqli error reporting enabled. Just add this line before new mysqli() in your code.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

这篇关于多个查询相互依赖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！