【MySQL学习笔记】第13章 熟练使用事务

13.1 什么是存储引擎

13.1.1 什么是存储引擎

MySQL的功能大致分为两种

一个是连接客户端和提前检查SQL语句内容的功能，即数据库处理的前台部分；

另一个是根据前台部分的指示，完成查询和文件操作等工作的功能，即后台部分。这个后台部分称为存储引擎。

13.1.2 存储引擎的种类

MySQL中预置了多个存储引擎，用户可以根据使用目的和个人喜好进行选择。

另外，每个表都可以单独指定存储引擎，比如“A表示xx存储引擎，B表是yy存储引擎”。

存储引擎相互独立且允许用户自主进行选择，正是MySQL的特征。

MySQL中可以使用的主要存储引擎

存储引擎	特征
MyISAM	MySQL5.4及以前版本默认的存储引擎。能够高速运行，但不支持事务和外键。
InnoDB	MySQL5.5或更高版本的默认存储引擎。是唯一一个支持事务的存储引擎
BLACKHOLD	写入的任何数据都会消失，查询始终返回空结果。主要用于复制。
MERGE	将多个MyISAM表作为一个表进行处理。也称为MRG_MyISAM
CSV	将数据的实体保存为CSV（逗号分隔）格式的文件文本。改文件可以直接通过Excel等打开。
MEMORY	因为数据全部储存在了内存中，所以处理数据特别快。主要作为从临时工作区和其他表中提取的数据的读取专用缓存使用。不支持事务。
ARCHIVE	可以通过压缩来存储大量数据，但仅支持INSERT和SELECT。

如果使用方法比较复杂，就需要对每一个存储引擎进行根伟细致的调优，同事还要研究新的存储引擎。

13.2 设置存储引擎

13.2.1 确认存储引擎

确认使用过表的存储引擎。显示表的详细信息使用SHOW CREATE TABLE命令。

mysql> SHOW CREATE TABLE tb;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb    | CREATE TABLE `tb` (
  `empid` varchar(10) DEFAULT NULL,
  `sales` int(10) DEFAULT NULL,
  `month` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

我们可以在ENGINE=XX的部分确认存储引擎。在上面的示例中，表使用了InnoDB。当创建表时，在未指定存储引擎的情况下，将默认选择InnoDB。

注：使用“\G”代替“；”

像“SHOW CREATE TABLE tb;”这样的命令，1行中会显示很多数据，所以易读性较差。这时可以在命令的末尾使用“\G”代替“；”。“G”一定要大写。

使用“\G”后，显示出来的结果就能像下面这样按列纵向显示，更易于阅读。

mysql> SHOW CREATE TABLE tb\G
*************************** 1. row ***************************
       Table: tb
Create Table: CREATE TABLE `tb` (
  `empid` varchar(10) DEFAULT NULL,
  `sales` int(10) DEFAULT NULL,
  `month` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

13.2.2 修改存储引擎

命令格式：ALTER TABLE 表名 ENGINE=存储引擎名；

练习：将表tb1B的存储引擎由InnoDB修改为MyISAM。

输入&执行结果：

mysql> ALTER TABLE tb1B ENGINE=MyISAM;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb1B\G
*************************** 1. row ***************************
       Table: tb1B
Create Table: CREATE TABLE `tb1b` (
  `empid` varchar(10) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

13.3 什么是事务

将多个操作作为单个逻辑工作单元处理的功能称为事务（transaction）。将事务开始之后的处理结果反映到数据库中的操作称为提交（commit），不反应到数据库中而是恢复为原来状态的操作称为回滚（rollback）。

13.4 使用事务

13.4.1 执行前的注意事项

使用无用的数据进行练习，数据一旦删除可能无法复原；

储存引擎为MyISAM的表无法使用事务功能，确认表tb的存储引擎是默认的InnoDB之后再使用事务。

13.4.2 开启事务

首先，显示表tb中的数据：

mysql> SELECT * FROM tb;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A104  |   181 |     4 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
| A102  |    54 |     5 |
| A102  |   205 |     6 |
+-------+-------+-------+
10 rows in set (0.01 sec)

开启事务命令：START TRANSACTION;

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

删除表tb中的所有记录

mysql> DELETE FROM tb;
Query OK, 10 rows affected (0.00 sec)

此时表的一部分功能会加锁（LOCK）。因此，在其他会话中不能对该表执行INSERT等操作。如果此时想要在其他会话中操作表tb，需要在开启了事务的MySQL监视器中执行“COMMIT”或“ROLLBACK”，才能进行处理。

13.4.3 确认表的内容

现在表tb中已经没有记录了

mysql> DELETE FROM tb;
Query OK, 0 rows affected (0.00 sec)

回滚复原

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

mysql> SELECT * FROM tb;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A102  |    54 |     5 |
| A104  |   181 |     4 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
+-------+-------+-------+
10 rows in set (0.00 sec)

注意：当执行“ROLLBACK”时，事务会被关闭。下次使用时必须再次使用“START TRANSACTION;”开启事务。

在上面的练习中如果执行“COMMIT;”代替“ROLLBACK”，删除记录的结果就会提交到数据库中，所有的记录都会被永久删除。

13.5 自动提交功能

在MySQL中执行命令，处理通常会自动提交。也就是说，所有的命令都会自动COMMIT。

在默认状态下，自动提交功能处于开启状态。但是当存储引擎为InnoDB，如果执行了START TRANSACTION(或 BEGIN)，在执行COMMIT命令之前就不会提交，回滚操作（ROLLBACK）才可以执行。

用户也可以强制将自动提交功能设置为关闭。如果关闭了自动提交功能，即使执行SQL语句也不会自动提交，必须通过COMMIT进行提交，或者通过ROLLBACK进行复原。

13.5.1 关闭自动提交功能

命令格式：SET AUTOCOMMIT=0;

练习：关闭自动提交功能后，向表tb插入（'test',555,555）;

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tb VALUES('test',555,555);
Query OK, 1 row affected (0.01 sec)

执行“SELECT * FROM tb；”，可以显示出INSERT的记录

mysql> SELECT * FROM tb;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A102  |    54 |     5 |
| A104  |   181 |     4 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
| test  |   555 |   555 |
+-------+-------+-------+
11 rows in set (0.00 sec)

执行回滚操作

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

mysql> SELECT * FROM tb;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A102  |    54 |     5 |
| A104  |   181 |     4 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
+-------+-------+-------+
10 rows in set (0.00 sec)

可以看到数据没有自动提交。想要提交实际修改的数据，必须执行“COMMIT；”。

注意：在自动提交功能关闭的状态下，如果没有进行COMMIT就推出MySQL，工作内容就不会反应到数据库中。

13.5.2 启动已关闭的自动提交功能

命令格式：SET AUTOCOMMIT=1;

mysql> SET AUTOCOMMIT=1;
Query OK, 0 rows affected (0.00 sec)

注：如何确认当前自动提交功能的模式？

命令格式：SELECT @@AUTOCOMMIT;

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

13.5.3 事务的使用范围

启用事务后，并不是所有操作都可以通过回滚复原，如：DROP DATABASE; DROP TABLE;  DROP VIEW;  ALTER TABLE;。