問題描述

在正確使用 mysqli 自動提交時遇到問題.以下是查詢.

Having trouble with proper usage of mysqli autocommit. Below are the queries.

Table1 和 Table3 是 InnoDB 而 Table2 是 MyISAM

Table1 and Table3 are InnoDB while Table2 is MyISAM

表 2 和表 3 的值已正確插入，但表 1 的值未存儲.運行代碼時沒有出現錯誤.

Values to Table2 and Table3 are inserted properly but values to Table1 are not being stored. No errors occur while running the code.

$dbconnect->autocommit(false);

$stmt = $dbconnect->prepare("INSERT INTO `table1`(`col1`,`col2`) VALUES (?,?)");
$stmt->bind_param('ss',$val1,$val2);
$stmt->execute();
$dbconnect->rollback();

$stmt = $dbconnect->prepare("INSERT INTO `table2`(`col1`,`col2`) VALUES (?,?)");
$stmt->bind_param('ss',$val3,$val4);
$stmt->execute();
$dbconnect->rollback();

$stmt = $dbconnect->prepare("INSERT INTO `table3`(`col1`,`col2`) VALUES (?,?)");
$stmt->bind_param('ss',$val5,$val6);
$stmt->execute();

$dbconnect->commit();

何時以及如何使用 autocommit(false) 和 rollback()?

When and how do you use autocommit(false) and rollback()?

推薦答案

當您有一系列 sql 語句必須一起執行以保持數據庫中的一致性時，您可以使用它.將調用 commit 視為在游戲中建立保存點.任何時候調用 rollback 都會撤消上一次提交之前所做的一切.

You use it when you have a series of sql statements that must be performed together to maintain consistency in your database. Think of calling commit as establishing a save point in a game. Anytime you call rollback you undo everything that was done up to the previous commit.

想象一下，您需要在發票表中保存發票，在invoice_details 表中保存詳細信息，在付款表中保存付款.為了保持一致性，您需要確保這些都完成了或沒有完成.如果您在何處添加發票和詳細信息，然后插入付款失敗，那么您的數據庫將處于不一致狀態.

Imagine a situation where you need to save an invoice in your invoice table, details in your invoice_details table and payments in your payments table. To maintain consistency you need to make sure that these are all done or none of them is done. If you where to add the invoice and the details and then there was a failure on inserting the payment then your database is left in an inconsistent state.

通常這是使用像這樣的 try/catch 塊來完成的:

Normally this is accomplished using a try/catch block like this:

try {
    $dbconnect->autocommit(false);

    $stmt = $dbconnect->prepare("INSERT INTO `invoices`(`col1`,`col2`) VALUES (?,?)");
    $stmt->bind_param('ss',$val1,$val2);
    $stmt->execute();

    $stmt = $dbconnect->prepare("INSERT INTO `invoice_details`(`col1`,`col2`) VALUES (?,?)");
    $stmt->bind_param('ss',$val3,$val4);
    $stmt->execute();

    $stmt = $dbconnect->prepare("INSERT INTO `payments`(`col1`,`col2`) VALUES (?,?)");
    $stmt->bind_param('ss',$val5,$val6);
    $stmt->execute();

    $dbconnect->commit();
} catch(Exception $e){
    // undo everything that was done in the try block in the case of a failure.
    $dbconnect->rollback();

    // throw another exception to inform the caller that the insert group failed.
    throw new StorageException("I couldn't save the invoice");
}

這篇關于php mysqli 自動提交和回滾的正確使用的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！