MySQL Transaction

摘要：在本教程中，您将了解MySQL 事务以及如何使用COMMIT和ROLLBACK语句来管理 MySQL 中的事务。

MySQL 事务简介

为了了解 MySQL 中的事务是什么，让我们看一下在示例数据库中添加新销售订单的示例。添加销售订单的步骤如下：

• 首先，从orders表中查询最新的销售订单号，并使用下一个销售订单号作为新的销售订单号。
• 接下来，将新的销售订单插入orders表中。
• 然后，获取新插入的销售订单号
• 之后，将新的销售订单项目与销售订单编号一起插入到orderdetails表中
• 最后，从orders和orderdetails表中选择数据以确认更改

现在，想象一下，如果由于表锁定等某种原因导致上述一个或多个步骤失败，销售订单数据会发生什么情况？例如，如果将订单的项目添加到orderdetails表的步骤失败，您将得到一个空的销售订单。

这就是事务处理能够发挥作用的原因。 MySQL事务允许您执行一组MySQL操作，以确保数据库永远不会包含部分操作的结果。在一组操作中，如果其中一个操作失败，则会发生回滚以将数据库恢复到其原始状态。如果没有发生错误，则将整组语句提交到数据库。

MySQL事务语句

MySQL为我们提供了以下重要语句来控制事务：

• 要启动事务，请使用START TRANSACTION语句。 BEGIN或BEGIN WORK是START TRANSACTION的别名。
• 要提交当前事务并使其更改永久化，请使用COMMIT语句。
• 要回滚当前事务并取消其更改，请使用ROLLBACK语句。
• 要禁用或启用当前事务的自动提交模式，请使用SET autocommit语句。

默认情况下，MySQL 自动将更改永久提交到数据库。要强制 MySQL 不自动提交更改，请使用以下语句：

SET autocommit = 0;Code language: SQL (Structured Query Language) (sql)

或者

SET autocommit = OFFCode language: SQL (Structured Query Language) (sql)

您可以使用以下语句显式启用自动提交模式：

SET autocommit = 1;Code language: SQL (Structured Query Language) (sql)

或者

SET autocommit = ON;Code language: SQL (Structured Query Language) (sql)

MySQL 事务示例

我们将使用示例数据库中的orders和orderDetails表进行演示。

提交示例

为了使用事务，您首先必须将 SQL 语句分解为逻辑部分并确定何时应提交或回滚数据。

下面说明了创建新销售订单的步骤：

• 首先，使用START TRANSACTION语句启动事务。
• 接下来，从orders表中选择最新的销售订单编号，并使用下一个销售订单编号作为新的销售订单编号。
• 然后，将新的销售订单插入orders表中。
• 之后，将销售订单项目插入orderdetails表中。
• 最后，使用COMMIT语句提交事务。

或者，您可以从orders和orderdetails表中选择数据来检查新的销售订单。

以下是执行上述步骤的脚本：

-- 1. start a new transaction
START TRANSACTION;

-- 2. Get the latest order number
SELECT 
    @orderNumber:=MAX(orderNUmber)+1
FROM
    orders;

-- 3. insert a new order for customer 145
INSERT INTO orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
VALUES(@orderNumber,
       '2005-05-31',
       '2005-06-10',
       '2005-06-11',
       'In Process',
        145);
        
-- 4. Insert order line items
INSERT INTO orderdetails(orderNumber,
                         productCode,
                         quantityOrdered,
                         priceEach,
                         orderLineNumber)
VALUES(@orderNumber,'S18_1749', 30, '136', 1),
      (@orderNumber,'S18_2248', 50, '55.09', 2); 
      
-- 5. commit changes    
COMMIT;Code language: SQL (Structured Query Language) (sql)

要获取新创建的销售订单，请使用以下查询：

SELECT 
    a.orderNumber,
    orderDate,
    requiredDate,
    shippedDate,
    status,
    comments,
    customerNumber,
    orderLineNumber,
    productCode,
    quantityOrdered,
    priceEach
FROM
    orders a
        INNER JOIN
    orderdetails b USING (orderNumber)
WHERE
    a.ordernumber = 10426;Code language: SQL (Structured Query Language) (sql)

这是输出：

回滚示例

首先，登录MySQL数据库服务器，删除orders表中的数据：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM orders;
Query OK, 327 rows affected (0.03 sec)Code language: SQL (Structured Query Language) (sql)

从输出中可以看到，MySQL 确认orders表中的所有行均已删除。

其次，在单独的会话中登录MySQL数据库服务器并从orders表中查询数据：

mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|      327 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

在第二个会话中，我们仍然可以看到orders表中的数据。

我们在第一届会议上就做出了改变。然而，这些变化并不是永久性的。在第一个会话中，我们可以提交或回滚更改。

为了演示目的，我们将回滚第一个会话中的更改。

mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)Code language: SQL (Structured Query Language) (sql)

在第一个会话中，我们还将验证orders表的内容：

mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|      327 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

从输出中可以清楚地看到，更改已被回滚。

在本教程中，您学习了如何使用包括START TRANSACTION COMMI,和ROLLBACK MySQL 事务语句来管理事务。