PHP &mySQL:实现事务的简单代码 - Commit &回滚

2022-01-06 00:00:00 transactions php mysql commit rollback

我的平台:

PHP &mysql

PHP & mySQL

我的情况:

我正在尝试在我的代码中实现事务.我试图遵循示例，但没有太大帮助.我正在运行 3 个查询，我想以这样的方式编写一个事务，以便如果任何查询失败，整个事务应该回滚.我真的很感激一个简单、高效且非面向对象的 PHP 代码来实现这个目标.提前致谢.

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 code to achieve this goal. Thank you in advance.

我的 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?
}

推荐答案

你需要使用mysqli 扩展以使用此功能.

You need to use the mysqli extension to use this functionality.

参见:autocommit()、commit() 和 rollback()

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s
", 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.
", $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).
", $row[0]);
    /* Free result */
    mysqli_free_result($result);
}

/* Drop table myCity */
mysqli_query($link, "DROP TABLE myCity");

mysqli_close($link);
?>

相关文章