©
本文档使用 PHP中文网手册 发布
(PHP 5)
mysqli::rollback -- mysqli_rollback — 回退当前事务
面向对象风格
过程化风格
$link
)回退当前数据库的事务。
link
仅以过程化样式:由 mysqli_connect() 或 mysqli_init() 返回的链接标识。
成功时返回 TRUE
, 或者在失败时返回 FALSE
。
Example #1 mysqli::rollback() example
面向对象风格
<?php
$mysqli = new mysqli ( "localhost" , "my_user" , "my_password" , "world" );
if ( mysqli_connect_errno ()) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
$mysqli -> autocommit ( FALSE );
$mysqli -> query ( "CREATE TABLE myCity LIKE City" );
$mysqli -> query ( "ALTER TABLE myCity Type=InnoDB" );
$mysqli -> query ( "INSERT INTO myCity SELECT * FROM City LIMIT 50" );
$mysqli -> commit ();
$mysqli -> query ( "DELETE FROM myCity" );
if ( $result = $mysqli -> query ( "SELECT COUNT(*) FROM myCity" )) {
$row = $result -> fetch_row ();
printf ( "%d rows in table myCity.\n" , $row [ 0 ]);
$result -> close ();
}
$mysqli -> rollback ();
if ( $result = $mysqli -> query ( "SELECT COUNT(*) FROM myCity" )) {
$row = $result -> fetch_row ();
printf ( "%d rows in table myCity (after rollback).\n" , $row [ 0 ]);
$result -> close ();
}
$mysqli -> query ( "DROP TABLE myCity" );
$mysqli -> close ();
?>
过程化风格
<?php
$link = mysqli_connect ( "localhost" , "my_user" , "my_password" , "world" );
if ( mysqli_connect_errno ()) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
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" );
mysqli_commit ( $link );
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 ]);
mysqli_free_result ( $result );
}
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 ]);
mysqli_free_result ( $result );
}
mysqli_query ( $link , "DROP TABLE myCity" );
mysqli_close ( $link );
?>
以上例程会输出:
0 rows in table myCity. 50 rows in table myCity (after rollback).
[#1] Steven McCoy [2012-02-10 21:16:44]
Remember that MyISAM tables do not support rollbacks.
I just drove myself crazy for an afternoon trying to figure out what was wrong with my code - meanwhile it was fine all along
[#2] xcalibur at xcalibur dot dk [2010-03-10 03:04:36]
Just a note about auto incremental ids and rollback.
When using transactions and inserting into a table containing a column with auto incremental ids, the id will be incremented even though the transaction is rolled back.
This might occupy a lot of ids if a lot of rollbacks are performed.
Example:
<?php
$mysqli = new mysqli("localhost", "gugbageri", "gugbageri", "gugbageri");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->autocommit(FALSE);
$mysqli->query("CREATE TABLE TestTable ( `id_column` INT NOT NULL AUTO_INCREMENT , `content` INT NOT NULL , PRIMARY KEY ( `id_column` )) ENGINE = InnoDB;");
$mysqli->commit();
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->commit();
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->rollback();
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->commit();
if ($result = $mysqli->query("SELECT id_column FROM TestTable")) {
while($row = $result->fetch_row()) {
printf("Id: %d.\n", $row[0]);
}
$result->close();
}
$mysqli->query("DROP TABLE TestTable");
$mysqli->close();
?>
This will output:
Id: 1.
Id: 5.
[#3] jd at dilltree dot com [2009-07-22 16:08:17]
Something to consider when using transact is that you should not perform a normal query on the same table (such as a DELETE) immediately after a transaction. If the transaction rolls-back, the DELETE will execute and even show affected rows, but the row can be magically re-inserted even if the rollback() command comes before the DELETE query.
[#4] Lorenzo - webmaster AT 4tour DOT it [2009-02-10 06:31:00]
This is an example to explain the powerful of the rollback and commit functions.
Let's suppose you want to be sure that all queries have to be executed without errors before writing data on the database.
Here's the code:
<?php
$all_query_ok=true; // our control variable
//we make 4 inserts, the last one generates an error
//if at least one query returns an error we change our control variable
$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false;
$mysqli->query("INSERT INTO myCity (id) VALUES (200)") ? null : $all_query_ok=false;
$mysqli->query("INSERT INTO myCity (id) VALUES (300)") ? null : $all_query_ok=false;
$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false; //duplicated PRIMARY KEY VALUE
//now let's test our control variable
$all_query_ok ? $mysqli->commit() : $mysqli->rollback();
$mysqli->close();
?>
hope to be helpful!