PHP mysqli autocommit() 方法

 PHP MySQLi 函数

PHP mysqli::autocommit() / mysqli_autocommit() 函数用于打开或关闭自动提交数据库连接查询模式。要确定自动提交的当前状态，请使用 SQL 命令SELECT @@autocommit。

语法

复制代码复制代码//面向对象风格
public mysqli::autocommit(enable)

//面向过程风格
mysqli_autocommit(mysql, enable)
1
2
3
4
5

参数

返回值

成功则返回 true，成功则返回 false

示例:面向对象风格

下面的示例展示了mysqli::autocommit()方法的用法。

复制代码复制代码<?php
//建立与数据库的连接
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//表引擎必须支持事务
$mysqli->query("CREATE TABLE IF NOT EXISTS Employee (
  Name VARCHAR(255) NOT NULL,
  Salary DECIMAL(18,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

//关闭自动提交
$mysqli->autocommit(false);

$result = $mysqli->query("SELECT @@autocommit");
$row = $result->fetch_row();
printf("Autocommit is %s\n", $row[0]);

try {
  //准备插入语句
  $stmt = $mysqli->prepare('INSERT INTO Employee(Name, Salary) VALUES (?,?)');
  $stmt->bind_param('sd', $name, $salary);

  //插入一些值
  $name = 'John';
  $salary = 3000;
  $stmt->execute();
  $name = 'Marry';
  $salary = 2750;
  $stmt->execute();

  //提交数据库中的数据。这不会设置 autocommit=true
  $mysqli->commit();
  print "Committed 2 rows in the database\n";

  $result = $mysqli->query("SELECT @@autocommit");
  $row = $result->fetch_row();
  printf("Autocommit is %s\n", $row[0]);

  //尝试插入更多值
  $name = 'Kim';
  $salary = 3100;
  $stmt->execute();
  $name = 'Ramesh';
  $salary = 3000;
  $stmt->execute();

  //设置autocommit=true会触发提交
  $mysqli->autocommit(true);

  print "Committed 2 row in the database\n";

} catch (mysqli_sql_exception $exception) {
  $mysqli->rollback();

  throw $exception;
}
?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

上述代码的输出将是:

复制代码复制代码Autocommit is 0
Committed 2 rows in the database
Autocommit is 0
Committed 2 row in the database
1
2
3
4

示例:面向过程风格

下面的示例显示mysqli_autocommit()的用法 函数。

复制代码复制代码<?php
//建立与数据库的连接
$mysqli = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//表引擎必须支持事务
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS Employee (
  Name VARCHAR(255) NOT NULL,
  Salary DECIMAL(18,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

//关闭自动提交
mysqli_autocommit($mysqli, false);

$result = mysqli_query($mysqli, "SELECT @@autocommit");
$row = mysqli_fetch_row($result);
printf("Autocommit is %s\n", $row[0]);

try {
  //准备插入语句
  $stmt = mysqli_prepare($mysqli, 'INSERT INTO Employee(Name, Salary) VALUES (?,?)');
  mysqli_stmt_bind_param($stmt, 'sd', $name, $salary);

  //插入一些值
  $name = 'John';
  $salary = 3000;
  mysqli_stmt_execute($stmt);
  $name = 'Marry';
  $salary = 2750;
  mysqli_stmt_execute($stmt);

  //提交数据库中的数据。这不会设置 autocommit=true
  mysqli_commit($mysqli);
  print "Committed 2 rows in the database\n";

  $result = mysqli_query($mysqli, "SELECT @@autocommit");
  $row = mysqli_fetch_row($result);
  printf("Autocommit is %s\n", $row[0]);

  //尝试插入更多值
  $name = 'Kim';
  $salary = 3100;
  mysqli_stmt_execute($stmt);
  $name = 'Ramesh';
  $salary = 3000;
  mysqli_stmt_execute($stmt);

  //设置autocommit=true会触发提交
  mysqli_autocommit($mysqli, true);

  print "Committed 2 row in the database\n";
} catch (mysqli_sql_exception $exception) {
  mysqli_rollback($mysqli);

  throw $exception;
}
?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

上述代码的输出将是:

复制代码复制代码Autocommit is 0
Committed 2 rows in the database
Autocommit is 0
Committed 2 row in the database
1
2
3
4