mysql8学习笔记7--事务使用的控制

默认情况下，MySQL在启用自动提交模式的情况下运行。这意味着，当不在事务内时，每个语句都是原子的，就像它被START TRANSACTION和包围一样COMMIT。不能ROLLBACK用来撤消效果。但是，如果在语句执行期间发生错误，则会回滚该语句。

根据mysql参考手册：

13.3.1 START TRANSACTION，COMMIT和ROLLBACK语句

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

中括号里的修饰符号，后面再看

这些语句提供对事务使用的控制：

START TRANSACTION或 BEGIN开始新交易。
COMMIT 提交当前事务，使其更改永久生效。
ROLLBACK 回滚当前事务，取消其更改。
SET autocommit 禁用或启用当前会话的默认自动提交模式。

要隐式禁用单个语句系列的自动提交模式，请使用以下START TRANSACTION 语句：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

使用时START TRANSACTION，自动提交保持禁用状态，直到您使用COMMIT或结束事务 ROLLBACK。然后，自动提交模式将恢复为之前的状态。

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> update customers4  set cust_address='中国深圳龙岗区',cust_city='深圳' where cust_id=1;
Query OK, 1 row affected (0.37 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | big       | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

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

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | big       | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update customers4  set cust_name='small' where cust_id=1;                                                   
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | small     | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

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

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | big       | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update customers4  set cust_name='small' where cust_id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | small     | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | small     | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

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

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | small     | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql>

要显式禁用自动提交模式，请使用以下语句：

SET autocommit=0;

mysql> SET autocommit=0;
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | small     | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> update customers4  set cust_name='middle' where cust_id=1;     
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | middle    | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | small     | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> update customers4  set cust_name='middle' where cust_id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | middle    | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | middle    | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

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

mysql> select * from customers4;
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address          | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | middle    | 中国深圳龙岗区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
|       2 | big       | 中国深圳南山区        | 深圳      | NULL       | NULL     | NULL         | NULL         | NULL       |
+---------+-----------+-----------------------+-----------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql>