mysql系列详解四：mysql事务-技术流ken

mysql> use jobs; 2 mysql> show table status like "teachers"\G 3 *************************** 1. row *************************** 4 Name: teachers 5 Engine:  MyISAM << 6 Version: 10 7 Row_format: Dynamic 8 Rows: 0 9 Avg_row_length: 0 10 Data_length: 16384 11 Max_data_length: 0 12 Index_length: 0 13 Data_free: 0 14 Auto_increment: 5 15 Create_time: 2018-10-19 18:20:52 16 Update_time: 2018-10-19 18:22:59 17 Check_time: NULL 18 Collation: utf8_general_ci 19 Checksum: NULL 20 Create_options: 21 Comment: 22 1 row in set (0.00 sec)

 

修改表引擎为InnoDB

 1 mysql> alter table teachers engine=innodb;
 2 Query OK, 1 row affected (0.03 sec)
 3 Records: 1  Duplicates: 0  Warnings: 0
 4 
 5 mysql> show table status like "teachers"\G
 6 *************************** 1. row ***************************
 7            Name: teachers
 8          Engine: InnoDB
 9         Version: 10
10      Row_format: Dynamic
11            Rows: 0
12  Avg_row_length: 0
13     Data_length: 16384
14 Max_data_length: 0
15    Index_length: 0
16       Data_free: 0
17  Auto_increment: 5
18     Create_time: 2018-10-20 04:17:01
19     Update_time: 2018-10-20 04:17:01
20      Check_time: NULL
21       Collation: utf8_general_ci
22        Checksum: NULL
23  Create_options: 
24         Comment: 
25 1 row in set (0.00 sec)

 

回滚事务演示

 1 mysql> start transaction;               <<启动事务
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> select * from teachers;          <<查看表信息
 5 +-----+------------+-----+--------+
 6 | TID | Name       | Age | Gender |
 7 +-----+------------+-----+--------+
 8 |   1 | Song Jiang |  45 | M      |
 9 +-----+------------+-----+--------+
10 1 row in set (0.00 sec)
11 
12 mysql> delete from teachers;           <<删除表中数据
13 Query OK, 1 row affected (0.01 sec)
14 
15 mysql> select * from teachers;         <<查看表中数据，现在数据为空
16 Empty set (0.00 sec)
17 
18 mysql> rollback;                       <<回滚事务       
19 Query OK, 0 rows affected (0.01 sec)
20 
21 mysql> select * from teachers;         <<再次查看数据，发现数据已经恢复
22 +-----+------------+-----+--------+
23 | TID | Name       | Age | Gender |
24 +-----+------------+-----+--------+
25 |   1 | Song Jiang |  45 | M      |
26 +-----+------------+-----+--------+
27 1 row in set (0.00 sec)

 

提交事务演示

mysql> start transaction;                      <<启动事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from teachers;                 <<查询表中数据
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
+-----+------------+-----+--------+
1 row in set (0.00 sec)

mysql> insert into teachers values (2,‘ken‘,25,‘M‘); <<向表中插入一些数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from teachers;                  <<再次查看表中数据
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
|   2 | ken        |  25 | M      |
+-----+------------+-----+--------+
2 rows in set (0.00 sec)

mysql> commit;                                <<进行提交数据操作
Query OK, 0 rows affected (0.00 sec)

mysql> select * from teachers;                 <<数据已经生效
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
|   2 | ken        |  25 | M      |
+-----+------------+-----+--------+
2 rows in set (0.00 sec)

 

设置和使用保存点演示

mysql> start transaction;              <<启动事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from teachers;         <<查看表中数据
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
|   2 | ken        |  25 | M      |
+-----+------------+-----+--------+
2 rows in set (0.00 sec)

mysql> savepoint ken1;                <<设置保存点
Query OK, 0 rows affected (0.00 sec)

mysql> delete from teachers;          <<删除表中全部的数据
Query OK, 2 rows affected (0.00 sec)

mysql> select * from teachers;        <<查看表中的信息，现在为空
Empty set (0.00 sec)

mysql> rollback to ken1;             <<回滚到刚才设置的ken1节点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from teachers;       <<再次查看表中信息，已经回到ken1时候的状态
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
|   2 | ken        |  25 | M      |
+-----+------------+-----+--------+
2 rows in set (0.00 sec)

mysql> commit;                       <<进行事务的提交，结束事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from teachers;       <<可以再查看一下表中信息，进行确认
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
|   2 | ken        |  25 | M      |
+-----+------------+-----+--------+
2 rows in set (0.00 sec)

 

 

关闭自动提交

mysql中一个sql语句就是一个事务，mysql系统默认是开启了事务提交的功能，可以使用如下命令进行查看。

mysql> show global variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

 

如果想要关闭自动提交的功能，可以试下如下命令进行操作。

mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

 

mysql系列详解四：mysql事务-技术流ken

标签：duplicate   使用   mysql事务   nsis   --   使用命令   资料   操作   创建