PHP - Manual: mysqli::commit

2024-11-23

mysqli::$connect_errno »

« mysqli::close

mysqli::commit

mysqli_commit

(PHP 5, PHP 7, PHP 8)

mysqli::commit -- mysqli_commit — 提交一个事务

说明

面向对象风格

mysqli::commit(): bool

过程化风格

mysqli_commit(mysqli $link): bool

提交数据库连接的当前事务

参数

mysql: 仅以过程化样式：由mysqli_connect() 或 mysqli_init() 返回的 mysqli 对象。

返回值

成功时返回 true，或者在失败时返回 false。

范例

示例 #1 mysqli::commit() example

面向对象风格


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE Language LIKE CountryLanguage");

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* commit transaction */
$mysqli->commit();

/* drop table */
$mysqli->query("DROP TABLE Language");

/* close connection */
$mysqli->close();
?>

过程化风格


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

/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* set autocommit to off */
mysqli_autocommit($link, FALSE);

mysqli_query($link, "CREATE TABLE Language LIKE CountryLanguage");

/* Insert some values */
mysqli_query($link, "INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
mysqli_query($link, "INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* commit transaction */
mysqli_commit($link);

/* close connection */
mysqli_close($link);
?>

参见

mysqli_autocommit() - 打开或关闭本次数据库连接的自动命令提交事务模式
mysqli_rollback() - 回退当前事务

add a note

User Contributed Notes 6 notes

down

snchzantonio at gmail dot com ¶

8 years ago


I never recomend to use the ? with only one value variant like: $var = expression ? $var  : other_value or $var = expression ? null  : other_value ,and php suport Exception catchin so,use it :)

here my opinion abut lorenzo's post:

  <?php

//variants combined

$mysqli->autocommit(FALSE);

try{

  $mysqli->query("INSERT INTO myCity (id) VALUES (100)") or throw new Exception('error!');

// or we can use

  if( !$mysqli->query("INSERT INTO myCity (id) VALUES (200)"){ 
    throw new Exception('error!'); 
  }

}catch( Exception $e ){
  $mysqli->rollback();
}
$mysqli->commit();

?>

down

mvanlamz ¶

13 years ago


Please note that calling mysqli::commit() will NOT automatically set mysqli::autocommit() back to 'true'.

This means that any queries following mysqli::commit() will be rolled back when your script exits.

down

Lorenzo - webmaster AT 4tour DOT it ¶

13 years ago


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!

down

NoMan2000 ¶

7 years ago


This is to clarify the Flags parameters and what they mean:

MYSQLI_TRANS_COR_AND_CHAIN: 

Appends "AND CHAIN" to mysqli_commit or mysqli_rollback.

MYSQLI_TRANS_COR_AND_NO_CHAIN: 

Appends "AND NO CHAIN" to mysqli_commit or mysqli_rollback.

MYSQLI_TRANS_COR_RELEASE: 

Appends "RELEASE" to mysqli_commit or mysqli_rollback.

MYSQLI_TRANS_COR_NO_RELEASE:

Appends "NO RELEASE" to mysqli_commit or mysqli_rollback.

To clarify those options:

The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. 

The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction.

down

zattechnology at gmail dot com ¶

3 years ago


When you have alot of transactions to make, say you are applying inserting items to the database from a loop, it will be better to use the mysqli_commit for this kind of process as it will only hit the database once.

//Wrong way
Example 1:

$con = mysqli_connect("host", "username", "password", "database") or die("Could not establish connection to database");

$users = ["chris", "james", "peter", "mark", "joe", "alice", "bob"]

for($i=0; $i<count($users); $i++){
       $user= $users[$i];
       $query = mysqli_query($con, "INSERT INTO users (username) VALUES ('$user') ");
} 

//Correct Way
Example 2

$con = mysqli_connect("host", "username", "password", "database") or die("Could not establish connection to database");

$users = ["chris", "james", "peter", "mark", "joe", "alice", "bob"]

//Turn off autocommit 
mysqli_autocommit($con, FALSE)

//Make some transactions
for($i=0; $i<count($users); $i++){
     $user= $users[$i];
     $query = mysqli_query($con, "INSERT INTO users (username) VALUES ('$user') ");
} 

//Make a one-time hit to the database 
mysqli_commit($con)

As with the Example 1, since we had 7 items in the list, this means that their will be a 7 times hit to our database which can really affect performance. But with the Example 2, since we already turned off autocommit this means that the transactions will be queued ontill will explicitly call mysqli_commit($con)

down

-15

Bob Johnson ¶

12 years ago


The compactness of Lorenzo's code is admirable.

However, it is a good idea to also check  $mysqli->affected_rows to make sure that the INSERT statement did not fail. 


<?php

$result_query = @mysqli_query($query, $connect);

                if (($result_query == false) &&

                   (mysqli_affected_rows($connect) == 0))

                 {

                    // verify the query executed completely and verify that it

                    // had impact on the table


                    $success = false;


                    // here also, the developer could choose to add a ROLLBACK

                    // statement

                }

?>

add a note

官方地址：https://www.php.net/manual/en/mysqli.commit.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 开发编程科技 Elasticsearch HTML/CSS/XML 网络 JAVA NoSQL 数据库面试 C/C++ Golang Git 算法操作系统正则表达式 Redis 互联网 MySql JavaScript 运维软件国际架构设计 Mac OS TCP/IP Excel Vim Windows Socket Oracle VR MongoDB Python 运营 MemCache 商业硬件电子娱乐设计 nginx 摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 [PHP] inet_pton/inet_ntop IP地址转换函数 array_multisort() 多字段排序 PHPMailer设置utf8 PHPMailer字符集CharSet ADORecordSet对象 PHP 8.1 [鸟哥]PHP_INT_MIN 和 -9223372036854775808 adodb手册 opcache预加载 Composer的Packagist资源 mysql面试题 php扩展开发 PHP7 mcrypt替代方案 Packagist / Composer 中国全量镜像支持php7的性能采集扩展 [原创]诡异的php-fpm超时问题 laravel框架查看sql语句方式 php中几种注释规范

略微加速

PHP官方手册 - 互联网笔记

mysqli::commit

mysqli_commit

说明

参数

返回值

范例

参见

User Contributed Notes 6 notes