PHP & mySQL: 实现事务的简单代码 - 提交和回滚
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2133125/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP & mySQL: Simple code to implement Transaction - Commit & Rollback
提问by Devner
MY PLATFORM:
我的平台：
PHP & mySQL
PHP & MySQL
MY SITUATION:
我的情况：
I am trying to implement transactions within my code. I tried to follow examples, but it's not much help. I am running 3 queries and I wanted to write a transaction in such a way so that if any of the query(ies) fail, the whole transaction should roll back. I would really appreciate a simple, efficient and non-object oriented PHP codeto achieve this goal. Thank you in advance.
我正在尝试在我的代码中实现事务。我试图遵循示例，但没有太大帮助。我正在运行 3 个查询，我想以这样的方式编写一个事务，以便如果任何查询失败，整个事务应该回滚。我真的很感激一个简单、高效且非面向对象的 PHP 代码来实现这个目标。先感谢您。
MY PHP CODE:
我的 PHP 代码：
//db_res calls a custom function that performs a mysql_query on the query
$res1 = db_res("SELECT c1, c2 FROM t1 WHERE c5 = 3");
$res2 = db_res("UPDATE t2 SET c1 = 5 WHERE c2 = 10");
$res3 = db_res("DELETE FROM t3 WHERE c1 = 20");
if( $res1 && $res2 && $res3 )
{
//commit --- but how?
}
else
{
//rollback --- but how?
}
采纳答案by John Conde
You need to use the mysqli extensionto use this functionality.
您需要使用mysqli 扩展来使用此功能。
See: autocommit(), commit(), and rollback()
请参阅：autocommit()、commit()和rollback()
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* disable autocommit */
mysqli_autocommit($link, FALSE);
mysqli_query($link, "CREATE TABLE myCity LIKE City");
mysqli_query($link, "ALTER TABLE myCity Type=InnoDB");
mysqli_query($link, "INSERT INTO myCity SELECT * FROM City LIMIT 50");
/* commit insert */
mysqli_commit($link);
/* delete all rows */
mysqli_query($link, "DELETE FROM myCity");
if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
$row = mysqli_fetch_row($result);
printf("%d rows in table myCity.\n", $row[0]);
/* Free result */
mysqli_free_result($result);
}
/* Rollback */
mysqli_rollback($link);
if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
$row = mysqli_fetch_row($result);
printf("%d rows in table myCity (after rollback).\n", $row[0]);
/* Free result */
mysqli_free_result($result);
}
/* Drop table myCity */
mysqli_query($link, "DROP TABLE myCity");
mysqli_close($link);
?>
回答by rjmunro
You don't need to use mysqli. You can just issue the transaction commands as queries.
您不需要使用 mysqli。您可以将事务命令作为查询发出。
So for your example:
所以对于你的例子：
mysql_query("start transaction;");
//db_res calls a custom function that performs a mysql_query on the query
$res1 = db_res("SELECT c1, c2 FROM t1 WHERE c5 = 3");
$res2 = db_res("UPDATE t2 SET c1 = 5 WHERE c2 = 10");
$res3 = db_res("DELETE FROM t3 WHERE c1 = 20");
if( $res1 && $res2 && $res3 )
{
mysql_query("commit;");
}
else
{
mysql_query("rollback;");
}
By they way if you are thinking about upgrading to mysqli, please don't. Upgrade to PDO instead, it's much more sane.
顺便说一句，如果您正在考虑升级到 mysqli，请不要这样做。改为升级到 PDO，它更加理智。
