20 MySQL 事务处理 与存储引擎选择

事务是由1个或多个SQL语句组成的不可分割的整体;
各SQL语句间相互依赖,全部执行;或全部不执行;

1、事务处理的2种方法

事务控制语句:
rollback 回滚事务:结束未完成事务;并撤销正在进行的所有未确认修改;
commit 确认事务:确认后对数据库的所有修改成为永久性;
savepoint identifier 设置保存点:可设置多个保存点;
rollback to identifier 回滚到保存点;

1.1 用 begin/ rollback/ commit 实现:
begin或start transaction 开始一个事务;
rollback 回滚事务;
commit 确认事务;

1.2 直接用set改变MySQL自动提交模式:
set autocommit=0 禁止自动提交;
set autocommit=1 开启自动提交;

示例：

create table mytest(
	tid int primary key,
	tname varchar(10)
);

示例 commit

begin;	/* 开启事务 */
insert into mytest values(1,'test01');
insert into mytest values(2,'test02');
commit; /* 确认事务 */

mysql> select * from mytest;
+-----+--------+
| tid | tname  |
+-----+--------+
|   1 | test01 |
|   2 | test02 |
+-----+--------+
2 rows in set (0.00 sec)

示例 rollback

begin;
insert into mytest values(3,'test03');
insert into mytest values(4,'test04');

执行 test03/ test04后查询有4行；
但因为 没有 commit 确认，通过 rollback 可回滚，即第2次查询只有2行了

mysql> select * from mytest;
+-----+--------+
| tid | tname  |
+-----+--------+
|   1 | test01 |
|   2 | test02 |
|   3 | test03 |
|   4 | test04 |
+-----+--------+
4 rows in set (0.00 sec)

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

mysql> select * from mytest;
+-----+--------+
| tid | tname  |
+-----+--------+
|   1 | test01 |
|   2 | test02 |
+-----+--------+
2 rows in set (0.00 sec)

示例 savepoint

begin;
insert into mytest values(5,'test05');
savepoint s1;
insert into mytest values(6,'test06');
savepoint s2;
insert into mytest values(7,'test07');
rollback to s2;
commit;

注意 test07 被 rollback 回滚到 savepoint 保存点 s2

mysql> select * from mytest;
+-----+--------+
| tid | tname  |
+-----+--------+
|   1 | test01 |
|   2 | test02 |
|   5 | test05 |
|   6 | test06 |
+-----+--------+
4 rows in set (0.00 sec)

示例 set autocommit=0 禁止自动提交

set autocommit=0;
insert into mytest values(8,'test08');
rollback;

设置 set autocommit=0 后,事务不会自动执行;

2、通过 InnoDB 使用事务

InnoDB 是事务型数据库的首选引擎,支持事务安全表 ACID
MySQL 5.7 以后默认 InnoDB 引擎;
MySQL 5.5 以前默认 MyISAM引擎, 需事务处理时,另设置InnoDB引擎;

事务4大特性 ACID 事务安全
Atomicity 整体性: 全部执行;或全部不执行;
Consistency 一致性
Isolation 隔离性
Durability 持久性

MySQL的核心就是存储引擎, 可通过 show engines 查询当前支持的存储引擎类型

mysql> show engines;
+------------------+-------+------------------------------------------------------------+------------+---+----------+
|Engine            |Support|Comment                										|Transactions|XA |Savepoints|
+------------------+-------+------------------------------------------------------------+------------+---+----------+
|MEMORY            |YES    |Hash based, stored in memory, useful for temporary tables	|NO          |NO |NO        |
|MRG_MYISAM        |YES    |Collection of identical MyISAM tables                		|NO          |NO |NO        |
|CSV               |YES    |CSV storage engine                							|NO          |NO |NO        |
|PERFORMANCE_SCHEMA|YES    |Performance Schema                							|NO          |NO |NO        |
|MyISAM            |YES    |MyISAM storage engine                						|NO          |NO |NO        |
|InnoDB            |DEFAULT|Supports transactions, row-level locking, and foreign keys  |YES         |YES|YES       |
|BLACKHOLE         |YES    |dev/null storage engine(anything you write to it disappears)|NO          |NO |NO        |
|ARCHIVE           |YES    |Archive storage engine             							|NO          |NO |NO        |
+------------------+-------+------------------------------------------------------------+------------+---+----------+
9 rows in set (0.00 sec)

InnoDB 具有提交/回滚/崩溃恢复/ 巨大数据处理 等能力的事务安全存储引擎; 支持外键完整性约束;
MyISAM 拥有较高的插入/查询速度，不支持事务；
MEMORY 将数据表存储到内存中,为查询/引用数据表提供快速访问
注意:在MySQL守护进程崩溃时,所有 MEMORY 数据都会丢失,存在安全隐患

设置存储引擎
① 服务器端： 配置文件my.ini 的 [mysqld] 设置，
default-storage-engine=InnoDB
需重启MySQL服务器，才能生效；

② 客户端：仅对当前客户端有效, 对服务器无影响
set default_storage_engine=InnoDB

③ 创建表时设置存储引擎: engine= engine_name，如：

create table mytest(
	tid int primary key,
	tname varchar(10)
) engine=InnoDB default charset=utf8;

④ 修改表的存储引擎

alter table tab_name engine= engine_name;