MySQL-交易

MySQL-交易 (MySQL - 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.

事务是一组顺序的数据库操作，它们被当作一个单独的工作单元执行。 换句话说，除非组中的每个单独操作成功，否则交易将永远不会完成。 如果事务中的任何操作失败，则整个事务将失败。

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

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

交易性质 (Properties of Transactions)

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

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

• Atomicity − This 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.

  原子性 -确保工作单元内的所有操作均成功完成； 否则，事务将在失败点中止，并且先前的操作将回滚到它们以前的状态。

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

  一致性 -这可以确保数据库在成功提交事务后正确更改状态。

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

  隔离 -这使事务能够独立运行并且彼此透明。

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

  持久性 -这样可以确保在系统故障的情况下，持续执行已提交事务的结果或效果。

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

在MySQL中，事务以BEGIN WORK语句开始，以COMMIT或ROLLBACK语句结束。 开头和结尾语句之间SQL命令构成了事务的主体。

提交和回滚 (COMMIT and ROLLBACK)

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

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

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

  成功完成事务后，应发出COMMIT命令，以便对所有涉及的表所做的更改都将生效。

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

  如果发生故障，则应发出ROLLBACK命令以使事务中引用的每个表返回其先前状态。

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.

您可以通过设置名为AUTOCOMMIT的会话变量来控制事务的行为。 如果AUTOCOMMIT设置为1(默认值)，则每个SQL语句(无论是否在事务中)都被视为完整事务，并在完成时默认情况下提交。

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.

当AUTOCOMMIT设置为0时，通过发出SET AUTOCOMMIT = 0命令，随后的一系列语句就像一个事务，并且在发出明确的COMMIT语句之前，不会提交任何活动。

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

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

交易的一般例子 (A 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.

事件的顺序与所使用的编程语言无关。 可以使用用于创建应用程序的任何语言来创建逻辑路径。

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

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

• Begin transaction by issuing the SQL command BEGIN WORK.

  通过发出SQL命令BEGIN WORK开始事务。

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

  发出一个或多个SQL命令，例如SELECT，INSERT，UPDATE或DELETE。

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

  检查是否没有错误，一切都根据您的要求。

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

  如果有任何错误，则发出ROLLBACK命令，否则发出COMMIT命令。

MySQL中的交易安全表类型 (Transaction-Safe Table Types in MySQL)

You cannot use transactions directly, but for certain exceptions you can. However, they are not 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 the most popular one is InnoDB.

您不能直接使用事务，但是可以使用某些例外情况。 但是，它们并不安全且不能保证。 如果计划在MySQL编程中使用事务，则需要以特殊方式创建表。 表有很多类型，它们支持事务，但是最流行的一种是InnoDB 。

Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from the 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表的支持需要特定的编译参数。 如果您MySQL版本不支持InnoDB，请要求您的Internet服务提供商构建一个支持InnoDB表类型MySQL版本，或者下载并安装适用于Windows或Linux / UNIX的MySQL-Max Binary Distribution ，并在其中使用该表类型。开发环境。

If your MySQL installation supports InnoDB tables, simply add a TYPE = InnoDB definition to the table creation statement.

如果您MySQL安装支持InnoDB表，只需在表创建语句中添加TYPE = InnoDB定义。

For example, the following code creates an InnoDB table called tcount_tbl −

例如，以下代码创建一个名为tcount_tbl的InnoDB表-

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

mysql> use TUTORIALS;
Database changed

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

For more details on InnoDB, you can click on the following link −InnoDB

有关InnoDB的更多详细信息，您可以单击以下链接-InnoDB

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

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

翻译自: https://www.tutorialspoint.com/mysql/mysql-transactions.htm