Mysqli 简明教程

MySQLi - Transactions

事务是一组顺序的数据库操作，执行时就好像它是单个工作单元一样。换句话说，除非组内的每个操作都成功，否则事务永远不会完成。如果事务中的任何操作失败，整个事务都将失败。

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

实际上，你会将许多 SQL 查询组合到一个组中，并将它们全部作为事务的一部分一起执行。

Practically, you will club many SQL queries into a group and you will execute all of them together as part of a transaction.

Properties of Transactions

事务具有以下四个标准属性，通常用首字母缩写词 ACID 提到 -

Transactions have the following four standard properties, usually referred to by the acronym ACID −

  1. Atomicity − ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

  2. Consistency − ensures that the database properly changes states upon a successfully committed transaction.

  3. Isolation − enables transactions to operate independently on and transparent to each other.

  4. Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

在 MySQL 中，事务以 BEGIN WORK 语句开始，并以 COMMIT 或 ROLLBACK 语句结束。开始和结束语句之间的 SQLi 命令形成事务的主体。

In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT or a ROLLBACK statement. The SQLi commands between the beginning and ending statements form the bulk of the transaction.

COMMIT and ROLLBACK

这两个关键字 Commit 和 Rollback 主要用于 MySQL 事务。

These two keywords Commit and Rollback are mainly used for MySQL Transactions.

  1. When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect.

  2. If a failure occurs, a ROLLBACK command should be issued to return every table referenced in the transaction to its previous state.

你可以通过设置名为 AUTOCOMMIT 的会话变量来控制事务的行为。如果 AUTOCOMMIT 设置为 1（默认），则每个 SQL 语句（在事务中或不在事务中）都被视为一个完整的事务，并在完成后默认提交。当 AUTOCOMMIT 设置为 0，即发出 SET AUTOCOMMIT=0 命令时，后续一系列语句就像一个事务，并且在发出显式 COMMIT 语句之前不会提交任何活动。

You can control the behavior of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes. When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT=0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.

你可以使用 mysqli_query() 函数在 PHP 中执行这些 SQL 命令。

You can execute these SQL commands in PHP by using mysqli_query() function.

Generic Example on Transaction

这个事件序列与所使用的编程语言无关；逻辑路径可以在用于创建应用程序的任何语言中创建。

This sequence of events is independent of the programming language used; the logical path can be created in whichever language you use to create your application.

你可以使用 mysqli_query() 函数在 PHP 中执行这些 SQL 命令。

You can execute these SQL commands in PHP by using mysqli_query() function.

  1. Begin transaction by issuing SQL command BEGIN WORK.

  2. Issue one or more SQL commands like SELECT, INSERT, UPDATE or DELETE.

  3. Check if there is no error and everything is according to your requirement.

  4. If there is any error, then issue ROLLBACK command, otherwise issue a COMMIT command.

Transaction-Safe Table Types in MySQL

你不能直接使用事务，你可以使用它们，但它们并不安全且不能保证。如果你计划在 MySQL 编程中使用事务，则需要以特殊的方式创建表。有许多类型的表格支持事务，但最受欢迎的表格是 InnoDB 。

You can not use transactions directly, you can but they would not be safe and guaranteed. If you plan to use transactions in your MySQL programming, then you need to create your tables in a special way. There are many types of tables, which support transactions but most popular one is InnoDB.

对 InnoDB 表的支持要求从源代码编译 MySQL 时使用一个特定的编译参数。如果你的 MySQL 版本不支持 InnoDB，请让你的互联网服务提供商构建一个支持 InnoDB 表类型的 MySQL 版本或下载并安装适用于 Windows 或 Linux/UNIX 的 MySQL-Max 二进制分发并使用开发环境中的表类型工作。

Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source. If your MySQL version does not have InnoDB support, ask your Internet Service Provider to build a version of MySQL with support for InnoDB table types or download and install the MySQL-Max binary distribution for Windows or Linux/UNIX and work with the table type in a development environment.

如果你的 MySQL 安装支持 InnoDB 表，只需向表创建语句中添加一个 TYPE = InnoDB 定义即可。例如，以下代码创建一个名为 tutorials_innodb 的 InnoDB 表 -

If your MySQL installation supports InnoDB tables, simply add a TYPE = InnoDB definition to the table creation statement. For example, the following code creates an InnoDB table called tutorials_innodb −

root@host# mysql -u root -p;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table tutorials_innodb
   → (
   → tutorial_author varchar(40) NOT NULL,
   → tutorial_count  INT
   → ) TYPE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

查看以下链接以了解更多信息 - InnoDB

Check the following link to know more about − InnoDB

您可以使用其他表类型，如 GEMINI 或 BDB ，但它取决于您的安装是否支持这两种类型。

You can use other table types like GEMINI or BDB, but it depends on your installation if it supports these two types.