循环代码中的经典事务:
$mysqli->query("START TRANSACTION"); foreach ($pdata as $key => $value) { $sql = "INSERT INTO temp (`fund_id`) VALUES (" . $value . ")"; $result = $mysqli->query($sql); } $mysqli->query("COMMIT");
然后,我们更改为准备好的语句:
$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)这两个代码是否相同?我是否在第二个代码中缺少带有准备好的语句的内容?
2)$mysqli->commit()和$mysqli->query("COMMIT")?
$mysqli->commit()
$mysqli->query("COMMIT")
3)我是否需要$mysqli->query("START TRANSACTION");为准备好的语句块添加内容,否则设置后事务将自动开始autocommit(FALSE)?
$mysqli->query("START TRANSACTION");
autocommit(FALSE)
可以通过将prepareand bind_param语句拉出循环来优化循环。
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();
您已经关闭了行自动提交功能,autocommit(FALSE)因此不需要使用该START TRANSACTION语句。
START TRANSACTION