問(wèn)題描述

在正確使用 mysqli 自動(dòng)提交時(shí)遇到問(wèn)題.以下是查詢.

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 的值未存儲(chǔ).運(yùn)行代碼時(shí)沒(méi)有出現(xiàn)錯(cuò)誤.

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();

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

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

推薦答案

當(dāng)您有一系列 sql 語(yǔ)句必須一起執(zhí)行以保持?jǐn)?shù)據(jù)庫(kù)中的一致性時(shí)，您可以使用它.將調(diào)用 commit 視為在游戲中建立保存點(diǎn).任何時(shí)候調(diào)用 rollback 都會(huì)撤消上一次提交之前所做的一切.

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.

想象一下，您需要在發(fā)票表中保存發(fā)票，在invoice_details 表中保存詳細(xì)信息，在付款表中保存付款.為了保持一致性，您需要確保這些都完成了或沒(méi)有完成.如果您在何處添加發(fā)票和詳細(xì)信息，然后插入付款失敗，那么您的數(shù)據(jù)庫(kù)將處于不一致?tīng)顟B(tài).

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 塊來(lái)完成的:

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");
}

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