Skip to main content

MySQL事务:通过示例启动事务、提交和回滚

介绍MySQL事务

为了理解MySQL中的事务是什么,我们来看一个在示例数据库中添加新销售订单的示例。添加销售订单的步骤如下所述:

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

这就是为什么事务处理起到了解救作用。MySQL事务允许您执行一组MySQL操作,以确保数据库从不包含部分操作的结果。在一组操作中,如果其中一个操作失败,则发生回滚以将数据库恢复到其原始状态。如果没有发生错误,则将整个语句集提交到数据库。

MySQL事务语句

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

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

SET autocommit = 0;

SET autocommit = OFF

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

SET autocommit = 1;

SET autocommit = ON;

MySQL事务示例

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

MySQL Transaction: orders & orderDetails Tables

提交示例

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

以下说明了创建新销售订单的步骤:

或者,您可以从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;

MySQL Transaction Example

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

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;

以下是输出:

MySQL Transaction Example Order

回滚示例

首先,登录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)

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

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

mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
| 327 |
+----------+
1 row in set (0.00 sec)

在第二节课中,我们仍然可以看到orders表中的数据。

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

出于演示目的,我们将在第一节课中回滚更改。

mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)

在第一节课中,我们还将验证orders表的内容:

mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
| 327 |
+----------+
1 row in set (0.00 sec)

正如您可以从输出中清楚地看到的,这些更改已经回滚。

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