这里，我们使用 set session autocommit=0,修改当前会话的事务提交方式。autocommit变量有两个可选值：ON与OFF，ON代表事务是自动提交，OFF代表不自动提交。rollback手动回滚事务，commit手动提交事务。
一、第一次事务演示
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%autocommit%';
+
| Variable_name | Value |
+
| autocommit | OFF |
+
1 row in set (0.00 sec)
mysql> select * from mp_user;
+
| id | name | age | email |
+
| 1 | John1 | 21 | test1@baomidou.com |
| 2 | John1 | 22 | test2@baomidou.com |
| 3 | Tom | 28 | test3@baomidou.com |
| 4 | Jimmy | 21 | test4@baomidou.com |
| 5 | Billie | 24 | test5@baomidou.com |
| 6 | Smith | 20 | testinsert@baomidou.com |
| 7 | Smith2 | 21 | testinsert2@baomidou.com |
| 8 | Smith3 | 22 | testinsert3@baomidou.com |
| 9 | way2 | 1 | way2@baomidou.com |
| 10 | muti1 | 1 | muti1@baomidou.com |
| 11 | muti2 | 2 | muti2@baomidou.com |
| 12 | muti3 | 3 | muti3@baomidou.com |
| 13 | muti1 | 1 | muti1@baomidou.com |
+
13 rows in set (0.00 sec)
mysql> update mp_user set `name`='Bandle',age=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mp_user;
+
| id | name | age | email |
+
| 1 | Bandle | 10 | test1@baomidou.com |
| 2 | John1 | 22 | test2@baomidou.com |
| 3 | Tom | 28 | test3@baomidou.com |
| 4 | Jimmy | 21 | test4@baomidou.com |
| 5 | Billie | 24 | test5@baomidou.com |
| 6 | Smith | 20 | testinsert@baomidou.com |
| 7 | Smith2 | 21 | testinsert2@baomidou.com |
| 8 | Smith3 | 22 | testinsert3@baomidou.com |
| 9 | way2 | 1 | way2@baomidou.com |
| 10 | muti1 | 1 | muti1@baomidou.com |
| 11 | muti2 | 2 | muti2@baomidou.com |
| 12 | muti3 | 3 | muti3@baomidou.com |
| 13 | muti1 | 1 | muti1@baomidou.com |
+
13 rows in set (0.00 sec)
mysql> insert;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mp_user;
+
| id | name | age | email |
+
| 1 | John1 | 21 | test1@baomidou.com |
| 2 | John1 | 22 | test2@baomidou.com |
| 3 | Tom | 28 | test3@baomidou.com |
| 4 | Jimmy | 21 | test4@baomidou.com |
| 5 | Billie | 24 | test5@baomidou.com |
| 6 | Smith | 20 | testinsert@baomidou.com |
| 7 | Smith2 | 21 | testinsert2@baomidou.com |
| 8 | Smith3 | 22 | testinsert3@baomidou.com |
| 9 | way2 | 1 | way2@baomidou.com |
| 10 | muti1 | 1 | muti1@baomidou.com |
| 11 | muti2 | 2 | muti2@baomidou.com |
| 12 | muti3 | 3 | muti3@baomidou.com |
| 13 | muti1 | 1 | muti1@baomidou.com |
+
13 rows in set (0.00 sec)
思路
1.set session autocommit=0; – 关闭当前会话的事务自动提交，即需要手动提交。
2.update mp_user set name=‘Bandle’,age=10 where id=1; --更新mp_user表的第一条记录。
3.查看表记录，可以看到更新的痕迹。
4.insert; --紧接着，编写一条语法错误的SQL。下一步就是回滚。
5.rollback；–回滚事务，再次查看表中的记录，可以发现并未永久修改成功，即没有成功持久化。临时更新成功的记录并未写入磁盘。
二、第二次事务演示
mysql> show variables like '%autocommit%';
+
| Variable_name | Value |
+
| autocommit | OFF |
+
1 row in set (0.00 sec)
mysql> select * from mp_user;
+
| id | name | age | email |
+
| 1 | John1 | 21 | test1@baomidou.com |
| 2 | John1 | 22 | test2@baomidou.com |
| 3 | Tom | 28 | test3@baomidou.com |
| 4 | Jimmy | 21 | test4@baomidou.com |
| 5 | Billie | 24 | test5@baomidou.com |
| 6 | Smith | 20 | testinsert@baomidou.com |
| 7 | Smith2 | 21 | testinsert2@baomidou.com |
| 8 | Smith3 | 22 | testinsert3@baomidou.com |
| 9 | way2 | 1 | way2@baomidou.com |
| 10 | muti1 | 1 | muti1@baomidou.com |
| 11 | muti2 | 2 | muti2@baomidou.com |
| 12 | muti3 | 3 | muti3@baomidou.com |
| 13 | muti1 | 1 | muti1@baomidou.com |
+
13 rows in set (0.00 sec)
mysql> update mp_user set `name`='Bandle',age=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mp_user;
+
| id | name | age | email |
+
| 1 | Bandle | 100 | test1@baomidou.com |
| 2 | John1 | 22 | test2@baomidou.com |
| 3 | Tom | 28 | test3@baomidou.com |
| 4 | Jimmy | 21 | test4@baomidou.com |
| 5 | Billie | 24 | test5@baomidou.com |
| 6 | Smith | 20 | testinsert@baomidou.com |
| 7 | Smith2 | 21 | testinsert2@baomidou.com |
| 8 | Smith3 | 22 | testinsert3@baomidou.com |
| 9 | way2 | 1 | way2@baomidou.com |
| 10 | muti1 | 1 | muti1@baomidou.com |
| 11 | muti2 | 2 | muti2@baomidou.com |
| 12 | muti3 | 3 | muti3@baomidou.com |
| 13 | muti1 | 1 | muti1@baomidou.com |
+
13 rows in set (0.00 sec)
mysql> insert;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> commit;
Query OK, 0 rows affected (0.11 sec)
mysql> select * from mp_user;
+
| id | name | age | email |
+
| 1 | Bandle | 100 | test1@baomidou.com |
| 2 | John1 | 22 | test2@baomidou.com |
| 3 | Tom | 28 | test3@baomidou.com |
| 4 | Jimmy | 21 | test4@baomidou.com |
| 5 | Billie | 24 | test5@baomidou.com |
| 6 | Smith | 20 | testinsert@baomidou.com |
| 7 | Smith2 | 21 | testinsert2@baomidou.com |
| 8 | Smith3 | 22 | testinsert3@baomidou.com |
| 9 | way2 | 1 | way2@baomidou.com |
| 10 | muti1 | 1 | muti1@baomidou.com |
| 11 | muti2 | 2 | muti2@baomidou.com |
| 12 | muti3 | 3 | muti3@baomidou.com |
| 13 | muti1 | 1 | muti1@baomidou.com |
+
13 rows in set (0.00 s
思路
1.set session autocommit=0; – 关闭当前会话的事务自动提交，即需要手动提交。
2.update mp_user set name=‘Bandle’,age=10 where id=1; --更新mp_user表的第一条记录。
3.查看表记录，可以看到已有更新的痕迹。
4.insert; --紧接着，编写一条语法错误的SQL。但是，下面一步，我们不是回滚，而是提交。
5.commit；–提交事务，再次查看表中的记录，可以发现永久修改成功，也即成功持久化。临时更新成功的记录能够成功写入磁盘。
结论
修改提交模式为手动，即开启事务，而后，即使当前事务中有错误的SQL语句，但最后使用commit命令提交了事务，而不是回滚。那么，当前事务中，没有错误的那些SQL将会永久被执行，比如一条更新语句对记录的修改，将对磁盘中的记录做出永久性的更新。
|