MySQL学习笔记6——事务与临时表

一、事务

我们经常会遇到这样的场景：几个相互关联的数据操作，必须是全部执行，或者全部不执行，不可以出现部分执行的情况。

比如说，你从微信账号里提现100元到银行卡上，这个动作就包括了相互关联的2个步骤，首先是微信账号减100元，然后是银行卡账号加100元(这里假设没有手续费)。

假如因为某种异常，这2个操作只执行了一个，另外一个没有执行，就会出现你的钱少了100元，或者你的钱多了100 元的情况，这肯定是不能接受的。

如何才能确保多个关联操作全部执行呢？这时就要用到事务了。

1、什么事事务

事务是MySQL的一项功能，它可以使一组操作要么全部执行，要么全部不执行。

事务的语法结构：

START TRANSACTION 或者 BEGIN (开始事务)
一组DML语句（可包括增删改查）
COMMIT (提交事务)
ROLLBACK (事务回滚)

2、事务的特性

事务有四个主要特性：

• 原子性：表示事务中的操作要么全部执行，要么全部不执行，是一个整体，不能从中间打断。
• 一致性：表示数据的完整性不会因为事务的执行而受到破坏。
• 隔离性：表示多个事务同时执行的时候，不互相干扰。不同的隔离级别，相互独立的程度不同。
• 持久性：表示事务对数据的修改是永久有效的，不会因为系统故障而失效。

（1）事务的原子性和一致性

这里有一个需要注意的点，就是事务并不会自动处理SQL语句执行中的错误，如果对事务中的某一步数据操作发生的错误不做处理，继续提交的话，仍然会导致数据不一致。

如何使用事务，才能避免由于事务中的某一步或者几步操作出现错误，而导致数据不完整的情况发生呢？这就要用到事务中错误处理和回滚了:

• 如果发现事务中的某个操作发生错误，要及时使用回滚；
• 只有事务中的所有操作都可以正常执行，才进行提交。

那这里的关键就是判断操作是不是发生了错误。我们可以通过MySQL的函数ROW_COUNT()的返回，来判断一个DML操作是否失败，-1表示操作失败，否则就表示影响的记录数。

另外一个经常会用到事务的地方是存储过程。由于存储过程中包含很多相互关联的数据操作，所以会大量使用事务。我们可以在MySQL的存储过程中，通过获取SQL错误，来决定事务是提交还是回滚：

DELIMITER //					-- 修改分隔符为//
CREATE PROCEDURE demo.mytest() 	-- 创建存储过程
BEGIN							-- 开始程序体
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; -- 定义SQL操作发生错误是自动回滚
START TRANSACTION;				-- 开始事务
INSERT INTO demo.mytrans VALUES (1,5);
UPDATE demo.inventory SET invquantity = invquantity - 5;
COMMIT;							-- 提交事务
END
//								-- 完成创建存储过程
DELIMITER ;						-- 恢复分隔符为';'
CALL demo.mytest();				-- 调用存储过程
SELECT * FROM demo.mytrans;		-- 销售流水没有插入
SELECT * FROM demo.inventory;	-- 库存也没有消减，说明事务回滚了

这里，我们要先通过"DELIMITER //“语句把MySQL语句的结束标识改为”//”(默认语句的结束标识是";" )。

这样做的目的是告诉MySQL一直到"//" 才是语句的结束，否则，MySQL会在遇到第一个"," 的时候认为语句已经结束，并且执行。这样就会报错，自然也就没办法创建存储过程了。

创建结束以后，我们还要录入"//” ，告诉MySQL存储过程创建完成了，并且通过“DELIMITER ;“，再把语句结束标识改回到”;"

（2）事务的隔离性

MySQL有一种机制： "锁” 。MySQL可以把A中被修改过而且还没有提交的数据锁住，让B处于等待状态，一直到A提交完成，或者失败回滚，再释放锁，允许B读取这个数据。这样就可以防止因为A回滚而导致B读取错误的可能了。

MySQL中的锁有很多种，功能也十分强大。MySQL可以用锁来控制事务对数据的操作。通过对锁的使用，可以实现事务之间的相互隔离。锁的使用方式不同，隔离的程度也不同。

MySQL支持4种事务隔离等级：

• READ UNCOMMITTED：可以读取事务中还未提交的被更改的数据。
• READ COMMITTED：只能读取事务中已经提交的被更改的数据。
• REPEATABLE READ：表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这也是MySQL的默认选项。
• SERIALIZABLE：表示任何一个事务，一旦对某一个数据进行了任何操作，那么，一直到这个事务结束，MySQL都会把这个数据锁住，禁止其他事务对这个数据进行任何操作。

一般来讲，使用MySQL默认的隔离等级REPEATABLE READ就已经够了。不过，也不排除需要对一些关键的数据操作，使用最高的隔离等级SERIALIZABLE。计算完成之后，也不要忘记把隔离等级恢复到系统默认的状态，否则，会对日常的系统营运效率产生比较大的影响。

事务的隔离性对并发操作非常有用。当许多用户同时操作数据库的时候，隔离性可以确保各个连接之间互相不影响。这里我要提醒你的是，正确设置事务的隔离等级很重要。

• 对于一些核心的数据更改操作，你可能需要较高的隔离等级，比如涉及金额的修改；
• 你要考虑资源的消耗，不能使系统整体的效率受到太大的影响。所以，要根据具体的应用场景，正确地使用事务。

（3）总结

事务可以确保事务中的一系列操作全部被执行，不会被打断，或者全部不被执行，等待再次执行。事务中的操作，具有原子性、一致性、 永久性和隔离性的特征。但是这并不意味着，被事务包裹起来的一系列DML数据操作就一定会全部成功， 或者全部失败。你需要对操作是否成功的结果进行判断，并通知MySQL针对不同情况，分别完成事务提交或者回滚操作，才能最终确保事务中的操作全部成功或全部失败。

MySQL支持4种不同的事务隔离等级，等级越高，消耗的系统资源也越多，需要根据实际情况进行设定。

在MySQL中，并不是所有的操作都可以回滚。比如创建数据库、创建数据表、删除数据库、删除数据表等，这些操作是不可以回滚的，所以，你在操作的时候要特别小心，特别是在删除数据库、数据表时，最好先做备份，防止误操作。

二、临时表

当我们遇到一些复杂查询的时候，经常无法一步到位，或者是一步到位会导致查询语句太过复杂，开发和维护的成本过高。这个时候，就可以使用临时表。

1、临时表是什么

临时表是一种特殊的表，用来存储查询的中间结果，并且会随着当前连接的结束而自动删除。MySQL中有2种临时表，分别是内部临时表和外部临时表：

• 内部临时表主要用于性能优化，由系统自动产生，我们无法看到；
• 外部临时表通过SQL语句创建，我们可以使用。

临时表的创建语法结构：

CREATE TEMPORARY TABLE 表名
(
字段名 字段类型,
…
);

跟普通表相比，临时表有3个不同的特征:

• 临时表的创建语法需要用到关键字TEMPORARY;
• 临时表创建完成之后，只有当前连接可见，其他连接是看不到的，具有连接隔离性;
• 临时表在当前连接结束之后，会被自动删除。

因为临时表有连接隔离性，不同连接创建相同名称的临时表也不会产生冲突，适合并发程序的运行。而且，连接结束之后，临时表会自动删除，也不用担心大量无用的中间数据会残留在数据库中。因此，我们就可以利用这些特点，用临时表来存储SQL查询的中间结果。

2、如何用临时表简化复杂查询

CREATE TEMPORARY TABLE demo.mysales
SELECT				-- 用查询的结果直接生成临时表
itemnumber,
SUM(quantity) AS QUANTITY,
SUM(salesvalue) AS salesvalue
FROM
demo.transactiondetails
GROUP BY itemnumber
ORDER BY itemnumber;
SELECT * FROM demo.mysales

需要注意的是，这里是直接用查询结果来创建的临时表。因为创建临时表就是为了存放某个查询的中间结果。直接用查询语句创建临时表比较快捷，而且连接结束后临时表就会被自动删除，不需要过多考虑表的结构设计问题(比如冗余、效率等)。

通过临时表，我们就可以把一个复杂的问题拆分成很多个前后关联的步骤，把中间的运行结果存储起来，用于之后的查询。这样一来， 就把面向集合的SQL查询变成了面向过程的编程模式，大大降低了难度。

3、内存临时表和磁盘临时表

由于采用的存储方式不同，临时表也可分为内存临时表和磁盘临时表，它们有着各自的优缺点。

关于内存临时表，有一点需要注意的是，可以通过指定引擎类型(比如
ENGINE=MEMORY)， 来告诉MySQL临时表存储在内存中。

CREATE TEMPORARY TABLE demo.mytrans
(
itemnumber int,
groupnumber int,
branchnumber int
) ENGINE = MEMORY;

接下来，我们在磁盘上创建一个同样结构的临时表。在磁盘上创建临时表时，只要我们不指定存储引擎，MySQL 会默认存储引擎是InnoDB，并且把表存放在磁盘上。

当然，临时表也有不足，比如会挤占空间。建议在使用临时表的时候，要从简化查询和挤占资源两个方面综合考虑，既不能过度加重系统的负担，同时又能够通过存储中间结果，最大限度地简化查询。