MySQL之临时表

写在前面

本文一起看下MySQL的临时表。

1：什么是临时表

通过create temporary table t语句创建的表，就是临时表，临时表的临时体现在其生命周期是和会话一样的，当会话结束，即连接关闭时MySQL会自动将创建的临时表执行删除操作，如下：

mysql> create temporary table t_tmp(age int)engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t_tmp;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                       |
+-------+----------------------------------------------------------------------------------------------------+
| t_tmp | CREATE TEMPORARY TABLE `t_tmp` (
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
1
2
3
4
5
6
7
8
9
10
11
12

虽然临时表的生命周期是会话级别的，但是在程序中显式的删除临时表永远是一个我们必须要做的动作(删除也是使用drop table语句如：drop table t_tmp;)，因为你不能保证任何场景下你所创建的临时表都会被合理的删除，比如使用线程池时，此时就不仅仅是临时表没有被删除而占用资源的问题了，还会因为后续的程序读取到前面程序在临时表中的数据，而造成bug，而且这种bug是很难发现的。所以，养成好习惯是很重要的。

2：临时表和内存表

• 内存表
  内存表指的是存储引擎为memory的表，建表语句是create table t()engine=memory,数据是保存在内存中的，因此如果是重启的话，数据不会保留，但表结构是保留的，可以看到，内存表就是正常的表，只不过是存储引擎为memory，且重启后数据不会保留，如下测试：

mysql> create table t_memory(id int primary key auto_increment)engine=memory;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t_memory value();
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_memory;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

// 重启
[root@localhost tmp]# service mysql restart
Shutting down MySQL............. SUCCESS! 
Starting MySQL................................................................. SUCCESS! 

// 重启后查看
mysql> show create table t_memory;
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| t_memory | CREATE TABLE `t_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t_memory;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

• 临时表
  可以是任何存储引擎，但是生命周期和会话绑定，并且不同会话可以创建相同名称的临时表，具体我们在后面继续来分析。

3：临时表的特点

为了便于理解，我们来看下下面这个操作序列：

总结其特点如下：

1：语法是create temporary table ...
2：临时表在会话之间是隔离的，即本会话只能看到本会话内创建的临时表
3：临时表可以和普通表同名
4：操作时，存在同名的临时表和普通表时，临时表的优先级高于普通表
5：show tables不会显示临时表，只显示普通表
6：不同会话可以创建同名的临时表
1
2
3
4
5
6

其中的特点6：不同会话可以创建同名的临时表当我们在实际业务代码中需要使用中间表的业务中就非常有用了，比如在分库分表场景中聚合不同库和表的数据，此时如果是使用普通表，那么不同的会话并行操作时肯定会出现表名称重复的问题，而使用临时表则会很好的解决这个问题。

4：为什么临时表是可以重名的

从前面的分析中我们知道了，不同的会话临时表名称是可以重复的，这是为什么呢？要解释这个问题，必须先来了解下，MySQL是如何判断表是否存在？，每个表都有一个对应的table_def_key，对于普通表table_def_key的定义是库名+表名，因此普通表的表名称不可以重复，而临时表table_def_key的规则是库名+表名+server_id+thread_id，而其中thread_id，每个会话连接都是不一样的，所以，临时表是可以重名的，那么当我们执行语句create temporary table tttt(age int(32))engine=innodb;之后临时表tttt的结构和数据都是如何存储的呢？对于结构是在select @@tmpdir目录下创建名称为#sql{进程 id}_{线程 id}_序列号.frm的文件，如下：

[root@localhost tmp]# mysql -uroot -p -e"select @@tmpdir"
Enter password: 
+----------+
| @@tmpdir |
+----------+
| /tmp     |
+----------+
[root@localhost tmp]# pwd
/tmp
[root@localhost tmp]# ll | egrep '#sql'
-rw-r----- 1 mysql mysql    8558 Sep  2 16:50 #sql105b4_3_0.frm
1
2
3
4
5
6
7
8
9
10
11

数据的存放，在5.7之前是在select @@tmpdir目录下创建一个相同前缀的.ibd文件，5.7之后引入了临时文件表空间，数据就存放在这里，就不需要生成ibd文件了。

写在后面

参考文章列表：

MySQL 临时表 。