Mysql 简明教程

MySQL - Transactions

The MySQL Transactions

MySQL 事务是一组连续的数据库操作，执行时如同一个单独的工作单元。换言之，一个事务永远不会完全完成，除非组中的每个单独操作成功完成。如果事务中的任何操作失败，则整个事务都将失败。

实际上，您会将多个 SQL 查询组合到一个组中，并将它们全部作为事务的一部分一起执行。这将确保没有数据丢失或 SQL 查询执行失败。

Properties of Transactions

事务有四个标准属性，通常用缩写 ACID 表示 −

  1. Atomicity - 这确保事务中的所有操作都被视为一个单一单元。事务中的所有操作都已成功完成，否则都不执行。如果事务的任何部分失败，整个事务将回滚，数据库将保留其原始状态。

  2. Consistency - 这确保在成功提交事务后，数据库会正确更改状态。

  3. Isolation - 这使事务能够独立、透明地相互操作。

  4. Durability - 这确保一旦提交事务，它对数据库的影响将是永久的，并且将在系统故障（例如断电、硬件故障）中存活。

Transactional Statements in MySQL

在 MySQL 中，事务以 START TRANSACTION 、 BEGIN 或 BEGIN WORK 语句开始，并以 COMMIT 或 ROLLBACK 语句结束。在开始语句和结束语句之间执行的 MySQL 命令构成了事务的主体。

要启用或禁用事务中的自动提交选项，可以使用 SET AUTOCOMMIT 命令。要启用自动提交，请将命令设置为“1”或“ON”，要禁用它，请将命令设置为“0”或“OFF”。

The COMMIT Command

COMMIT 命令是 MySQL 中的事务控制命令。发出该命令后，它将完成到目前为止事务对数据库表所做的更改，使这些更改变为永久。因此，这些更改将对 MySQL 中的其他活动会话可见。

Syntax

以下是 MySQL 中执行 COMMIT 命令的语法：

COMMIT;

Example

我们使用以下查询创建一个名为 CUSTOMERS 的表：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

我们正在向上面创建的表中插入一些记录 −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

显示的 CUSTOMERS 表如下：

使用以下查询启动一个事务并删除 CUSTOMERS 表中 AGE 为 25 的记录，然后 COMMIT 数据库中的更改：

START TRANSACTION;
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

Verification

表中将删除两行。要验证，使用以下 SELECT 语句显示修改后的 CUSTOMERS 表：

SELECT * FROM CUSTOMERS;

以下是所获得的输出 −

The AUTOCOMMIT Command

你可以通过设置会话变量 AUTOCOMMIT 来控制事务的行为。如果 AUTOCOMMIT 设置为 1（默认），则每个 SQL 语句（无论是否在事务中）都被视为一个完整的事务，并在完成后默认提交。

当 AUTOCOMMIT 设置为 0 时，通过发出 SET AUTOCOMMIT = 0 命令，一系列后续语句将充当一个事务，并且没有任何活动被提交，直到发出显式的 COMMIT 语句。

The ROLLBACK Command

ROLLBACK 命令是一个事务命令，用于撤销尚未保存（提交）到数据库的事务中所做的更改。此命令只能撤销自上次执行 COMMIT 或 ROLLBACK 语句以来所做事务的影响。

Syntax

以下是 MySQL 中 ROLLBACK 命令的语法：

ROLLBACK;

Example

使用以下查询删除 CUSTOMERS 表中 AGE 为 25 的记录，然后 ROLLBACK 数据库中的更改：

DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

Verification

此表不会受到影响。要验证，使用以下 SELECT 语句显示修改后的 CUSTOMERS 表：

SELECT * FROM CUSTOMERS;

以下是要获得的表：

你必须记住，ROLLBACK 仅在事务中起作用。如果你尝试在不启动事务的情况下执行它，则不会撤销更改。

The SAVEPOINT Command

保存点是在 MySQL 交易中一个逻辑回滚点。

执行 ROLLBACK 命令时，会将交易中所进行的更改还原成上一个 COMMIT 操作，或（如果未执行过任何 COMMIT）还原成交易的开头。但是，通过在交易中创建保存点，你可以建立特定点来部分回滚该交易。在两个提交点之间，你可以在交易中创建多个保存点，以获得多个回滚选项。

Syntax

在交易中创建 SAVEPOINT 命令的语法如下所示：

SAVEPOINT SAVEPOINT_NAME;

回滚到所创建的 SAVEPOINT 的语法如下：

ROLLBACK TO SAVEPOINT_NAME;

Example

在以下示例中，你计划从 CUSTOMERS 表中删除三条不同的记录，同时在每次删除前先创建 SAVEPOINT。这将允许你随时 ROLLBACK 到任何 SAVEPOINT，以将相应数据还原到其原始状态：

SAVEPOINT SP1;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=1;
Query OK, 1 row affected (0.01 sec)

SAVEPOINT SP2;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=2;
Query OK, 0 rows affected (0.00 sec)

SAVEPOINT SP3;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=3;
Query OK, 1 row affected (0.01 sec)

由于三个删除操作都已进行，让我们假设你已经改变想法，并决定 ROLLBACK 到标识为 SP2 的 SAVEPOINT。由于 SP2 是在第一次删除后创建的，因此此操作将撤消后两次删除：

ROLLBACK TO SP2;

Verification

如果你使用以下 SELECT 语句显示 CUSTOMERS 表，你将注意到自回滚到 SP2 后，仅进行了第一次删除：

SELECT * FROM CUSTOMERS;

获得的结果如下所示 −

Transaction-Safe Table Types in MySQL

在 MySQL 中，并不是所有表类型都本机支持交易。为了有效地使用交易，你应当以特定方式创建表。虽然可以使用各种表类型，最常用的事务安全表类型是 InnoDB。

要启用 InnoDB 表支持，你可能需要在编译 MySQL 源代码期间设置特定的编译参数。如果你的 MySQL 版本不包含 InnoDB 支持，你可以要求你的互联网服务提供商 (ISP) 提供支持 InnoDB 的 MySQL版本，或下载并安装 MySQL-Max Binary Distribution 以便在开发环境中使用 InnoDB 表。

如果你的 MySQL 安装支持 InnoDB 表，你可以按以下方式创建一个 InnoDB 表：

CREATE TABLE tcount_tbl (
   tutorial_author varchar(40) NOT NULL,
   tutorial_count  INT
) ENGINE = InnoDB;

以下是所获得的输出 −

Query OK, 0 rows affected (0.05 sec)

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

Transactions Using a Client Program

我们还可以使用客户端程序执行交易。

Syntax

Example

以下是这些程序 −