問題描述

循環代碼中的經典事務:

The classic transactions in a loop code:

$mysqli->query("START TRANSACTION");
foreach ($pdata as $key => $value) {
    $sql    = "INSERT INTO temp (`fund_id`) VALUES (" . $value . ")";
    $result = $mysqli->query($sql);
}
$mysqli->query("COMMIT");

然后我們更改為準備好的語句:

Then we change to prepared statements:

$mysqli->autocommit(FALSE);
foreach ($pdata as $key => $value) {
    $sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $value);
    $stmt->execute();
}
$mysqli->commit();

問題:

1) 這兩個代碼是否相同?我是否在帶有準備好的語句的第二個代碼中遺漏了什么?

1) Are these two codes identical? Am I missing something in the second code with prepared statements?

2) $mysqli->commit() 和 $mysqli->query("COMMIT") 一樣嗎?

2) Is $mysqli->commit() the same as $mysqli->query("COMMIT")?

3) 我是否需要為準備好的語句塊添加 $mysqli->query("START TRANSACTION"); 或者當我們設置 autocommit(FALSE) 時事務將自動啟動)?

3) Do I need to add $mysqli->query("START TRANSACTION"); for the prepared statements block or the transaction will automatically start when we set autocommit(FALSE)?

推薦答案

可以通過將 prepare 和 bind_param 語句拉出循環來優化循環.

Your loop can be optimized by pulling the prepare and bind_param statements out of the loop.

$value = null;
$mysqli->autocommit(FALSE);
$sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $value);
foreach ($pdata as $value) {
    $stmt->execute();
}
$mysqli->commit();

您已使用 autocommit(FALSE) 行關閉自動提交，因此不需要使用 START TRANSACTION 語句.

You have turned off autocommit with your autocommit(FALSE) line and therefore don't need to use the START TRANSACTION statement.

這篇關于PHP 在循環中準備語句和事務的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！