这里，我们使用 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@ || 2 | John1 | 22 | test2@ || 3 | Tom | 28 | test3@ || 4 | Jimmy | 21 | test4@ || 5 | Billie | 24 | test5@ || 6 | Smith | 20 | testinsert@ || 7 | Smith2 | 21 | testinsert2@ || 8 | Smith3 | 22 | testinsert3@ || 9 | way2 | 1 | way2@ || 10 | muti1 | 1 | muti1@ || 11 | muti2 | 2 | muti2@ || 12 | muti3 | 3 | muti3@ || 13 | muti1 | 1 | muti1@ |+----+--------+------+--------------------------+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: 0mysql> select * from mp_user;+----+--------+------+--------------------------+| id | name | age | email|+----+--------+------+--------------------------+| 1 | Bandle | 10 | test1@ || 2 | John1 | 22 | test2@ || 3 | Tom | 28 | test3@ || 4 | Jimmy | 21 | test4@ || 5 | Billie | 24 | test5@ || 6 | Smith | 20 | testinsert@ || 7 | Smith2 | 21 | testinsert2@ || 8 | Smith3 | 22 | testinsert3@ || 9 | way2 | 1 | way2@ || 10 | muti1 | 1 | muti1@ || 11 | muti2 | 2 | muti2@ || 12 | muti3 | 3 | muti3@ || 13 | muti1 | 1 | muti1@ |+----+--------+------+--------------------------+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 1mysql> rollback;Query OK, 0 rows affected (0.02 sec)mysql> select * from mp_user;+----+--------+------+--------------------------+| id | name | age | email|+----+--------+------+--------------------------+| 1 | John1 | 21 | test1@ || 2 | John1 | 22 | test2@ || 3 | Tom | 28 | test3@ || 4 | Jimmy | 21 | test4@ || 5 | Billie | 24 | test5@ || 6 | Smith | 20 | testinsert@ || 7 | Smith2 | 21 | testinsert2@ || 8 | Smith3 | 22 | testinsert3@ || 9 | way2 | 1 | way2@ || 10 | muti1 | 1 | muti1@ || 11 | muti2 | 2 | muti2@ || 12 | muti3 | 3 | muti3@ || 13 | muti1 | 1 | muti1@ |+----+--------+------+--------------------------+13 rows in set (0.00 sec)
思路
1.set session autocommit=0; – 关闭当前会话的事务自动提交，即需要手动提交。
2.update mp_user setname=‘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@ || 2 | John1 | 22 | test2@ || 3 | Tom | 28 | test3@ || 4 | Jimmy | 21 | test4@ || 5 | Billie | 24 | test5@ || 6 | Smith | 20 | testinsert@ || 7 | Smith2 | 21 | testinsert2@ || 8 | Smith3 | 22 | testinsert3@ || 9 | way2 | 1 | way2@ || 10 | muti1 | 1 | muti1@ || 11 | muti2 | 2 | muti2@ || 12 | muti3 | 3 | muti3@ || 13 | muti1 | 1 | muti1@ |+----+--------+------+--------------------------+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: 0mysql> select * from mp_user;+----+--------+------+--------------------------+| id | name | age | email|+----+--------+------+--------------------------+| 1 | Bandle | 100 | test1@ || 2 | John1 | 22 | test2@ || 3 | Tom | 28 | test3@ || 4 | Jimmy | 21 | test4@ || 5 | Billie | 24 | test5@ || 6 | Smith | 20 | testinsert@ || 7 | Smith2 | 21 | testinsert2@ || 8 | Smith3 | 22 | testinsert3@ || 9 | way2 | 1 | way2@ || 10 | muti1 | 1 | muti1@ || 11 | muti2 | 2 | muti2@ || 12 | muti3 | 3 | muti3@ || 13 | muti1 | 1 | muti1@ |+----+--------+------+--------------------------+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 1mysql> commit;Query OK, 0 rows affected (0.11 sec)mysql> select * from mp_user;+----+--------+------+--------------------------+| id | name | age | email|+----+--------+------+--------------------------+| 1 | Bandle | 100 | test1@ || 2 | John1 | 22 | test2@ || 3 | Tom | 28 | test3@ || 4 | Jimmy | 21 | test4@ || 5 | Billie | 24 | test5@ || 6 | Smith | 20 | testinsert@ || 7 | Smith2 | 21 | testinsert2@ || 8 | Smith3 | 22 | testinsert3@ || 9 | way2 | 1 | way2@ || 10 | muti1 | 1 | muti1@ || 11 | muti2 | 2 | muti2@ || 12 | muti3 | 3 | muti3@ || 13 | muti1 | 1 | muti1@ |+----+--------+------+--------------------------+13 rows in set (0.00 s
思路
1.set session autocommit=0; – 关闭当前会话的事务自动提交，即需要手动提交。
2.update mp_user setname=‘Bandle’,age=10 where id=1; --更新mp_user表的第一条记录。
3.查看表记录，可以看到已有更新的痕迹。
4.insert; --紧接着，编写一条语法错误的SQL。但是，下面一步，我们不是回滚，而是提交。
mit；–提交事务，再次查看表中的记录，可以发现永久修改成功，也即成功持久化。临时更新成功的记录能够成功写入磁盘。
结论
修改提交模式为手动，即开启事务，而后，即使当前事务中有错误的SQL语句，但最后使用commit命令提交了事务，而不是回滚。那么，当前事务中，没有错误的那些SQL将会永久被执行，比如一条更新语句对记录的修改，将对磁盘中的记录做出永久性的更新。
如果觉得《MySQL：事务：开启 回滚与提交》对你有帮助，请点赞、收藏，并留下你的观点哦！
