MySQL语句学习（七）--事务、表类型和存储引擎

一、事务

1、什么是事务？

事务是用于保证数据一致性，它由一组相关的DML语句组成，该组的DML语句要么全部成功，要么全部失败。

 

 当执行事务操作时，mysql会在表上加锁，防止其他用户该表的数据

MySQL数据库控制台事务几个重要操作：

1、start transaction--开始一个事务

2、savepoint 保存点--设置保存点

3、rollback to 保存点名--回退事务到保存点名那

4、roiiback --回退全部事务到开始位置

5、commit--提交事务，所有的操作生效，不能回退

-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27
    ( id INT, `name` VARCHAR(32)); 

-- 2. 开始事务
START TRANSACTION

-- 3. 设置保存点
SAVEPOINT a

-- 执行 dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;
SAVEPOINT b
-- 执行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');

-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态. ROLLBACK
COMMIT

 2、回退事务和提交事务

1、保存点（savepoint），保存事务中的点，用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。

2、提交事务：使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化，结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务后，其他对话（其他连接）将可以查看到事务变化后的新数据【所有数据正式生效----隔离结束】

3、事务细节讨论 

 1. 如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚

2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback， 默认就是回退到你事务开始的状态

3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.

4. 你可以在事务没有提交前，选择回退到哪个保存点--commit to 保存点

5. InnoDB 存储引擎支持事务 , MyISAM 不支持

6. 开始一个事务 start transaction, 或者set autocommit=off; 

-- 1. 如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300, 'milan'); -- 自动提交 commit
SELECT * FROM t27
-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400, 'king');
INSERT INTO t27 VALUES(500, 'scott');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT; 
-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa; -- 执行 dml , --savepoint bbb
-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, set autocommit=off;

二、事务的隔离级别

1、事务隔离级别介绍

多个连接开启各自事务操作数据库中的数据，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性和实时性。

如果不考虑隔离，可能会引发如下问题：

• 脏读
• 不可重复读
• 幻读

1、脏读（dirty read）：当啊一个事务读取到另一个事务尚未提交的改变（update、insert、delete）时，产生脏读。---事务读到另一个事务未提交的改变

2、不可重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做出的修改或删除，每次返回不同的结果集，此时发生不可重复读。---事务读到另一个事务已提交的修改或删除改变。

3、幻读（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。---事务读到另一个事务已提交的插入的改变。

2、事物的隔离级别

MySQL的事务隔离级别定义了事务与事务之间的隔离程度

 

 2.1、设置事务隔离级别

-- 1. 开了两个 mysql 的控制台
-- 2. 查看当前 mysql 的隔离级别
SELECT @@tx_isolation; 
-- mysql> SELECT @@tx_isolation; 
-- +-----------------+
-- | @@tx_isolation | 
-- +-----------------+
-- | REPEATABLE-READ | 
-- +-----------------+

-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 4. 创建表
CREATE TABLE `account`(
    id INT, `name` VARCHAR(32), money INT);
-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]

2.2、事务的ACID特性

三、mysql 表类型和存储引擎 

 1、表类型

1、MySQL表类型有存储引擎（Storage Engines）决定，主要包括MySAM、innoDB、Memory等。

2、MySQL数据表主要支持六种类型：CSV、Memory、ARCHIVE、MGR_MYISAM、MYISAM、InnobDB。

2、主要三种: MyISAM、InnoDB、MEMORY

1、mylsam不支持事务，也不支持外键，但访问速度快，对事务完整性没要求

2、innoDB存储引擎提供了具有提交，回滚和崩溃恢复能力的事务安全。但是比起mylsam存储引擎，innoDB写的处理效率差一些，会占用更多的磁盘空间以保留数据和索引。

3、memory存储引擎使用存在在内存中的内容来创建表，每个memory表只实际对应一个磁盘文件。memory类型的表访问非常快，因为他的数据放在内存中并且默认使用hash索引。但是一旦MySQL服务关闭，表中的数据就会丢掉的，但表的结构还在。

3、三种存储引擎使用 

-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是前面使用过. -- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
CREATE TABLE t28 (
    id INT, `name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT t1

INSERT INTO t28 VALUES(1, 'jack');
SELECT * FROM t28;
ROLLBACK TO t1


-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了 Mysql 服务，数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有 IO 读写) 3. 默认支持索引(hash 表)
CREATE TABLE t29 (
    id INT, `name` VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29
    VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB

4、如何选用存储引擎