PHP在循环中准备语句和事务

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

Questions:

问题：

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

1）这两个代码是否相同？我是否在准备好的陈述中遗漏了第二个代码中的内容？

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

2）$ mysqli-> commit（）与$ mysqli-> query（“COMMIT”）相同吗？

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)?

3）我是否需要添加$ mysqli-> query（“START TRANSACTION”）;对于准备好的语句块或者当我们设置autocommit（FALSE）时事务会自动启动？

1 个解决方案

#1

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

可以通过将prepare和bind_param语句拉出循环来优化循环。

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

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

您已使用自动提交（FALSE）行关闭自动提交，因此不需要使用START TRANSACTION语句。

#1