MySQL内存表和临时表的区别

在MySQL中有三种虚拟表：临时表、内存表、视图。

一、内存表

• 表结构在磁盘，数据和索引保存在内存里面，MySQL服务重启数据丢失,但表结构依旧存。
• 断开连接结构和数据依然存在
• 参数控制：max_heap_table_size
• 到达上限后报错
• 不能包含TEXT,BLOB等字段
• 内存表默认存储引擎是MEMORY

1.1 创建内存表,需要将 ENGINE 设置为 MEMORY

CREATE TABLE [IF NOT EXISTS]  内存表名
(
	...
) ENGINE = MEMORY;

1.2 删除内存表

DROP TABLE IF EXISTS tmp_table;

1.3 使用内存表的注意事项

（1）当MySQL服务重启之后，内存表的数据会丢失，表结构依旧存。
（2）多个session，创建表的名字不能一样。
（3）一个session创建会话后，对其他session也是可见的。
（4）data目录下只有tmp_memory.frm，表结构放在磁盘上，数据放在内存中。
（5）可以创建索引，删除索引，支持唯一索引。
（6）不影响主备，主库上插入的数据，备库也可以查到。
（7）show tables 语句可以查看得到表，show create table可以查看内存表。
（8）内存表不能包含BLOB或者TEXT列。
（9）内存表支持AUTO_INCREMENT列。

二、临时表

• 表结构和数据都在内存，并且只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
• 参数控制：tmp_table_size
• 到达上限后创建文件在磁盘上
• 可以包含TEXT, BLOB等字段。
• 临时表默认的存储引擎是MyISAM

2.1 创建临时表

# 1.直接创建
CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名
(
	...
);
# 2.通过复制创建临时表
CREATE TEMPORARY TABLE temp_table
AS SELECT * FROM t2;

2.2 删除临时表

1.自动删除:默认情况下，当断开与数据库的连接后，临时表就会自动被销毁
2.手动删除:DROP TABLE IF EXISTS temp_table;

2.3 使用临时表的注意事项

1. 临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。两个不同的连接可以使用相同名字的临时表，两个表之间不存在什么关系，如果临时表的名字和已经存在的磁盘表名字一样，那么临时表会暂时覆盖磁盘表。就是说，你select 查询，只会显示临时表里面的，不会显示磁盘表。
2. 临时表的存储引擎：memor，myisam，merge，innodb，临时表不支持mysql cluster簇。
3. 同一个查询语句，只能用一次临时表，就是说不能将表和自己做连接等。
4. 重命名表，不能用rename 可以用alter table代替。
5. 如果超出了临时表的容量，临时表会转换成磁盘表。
6. show tables语句不会列出临时表，在information_schema中也不存在临时表信息，show create table可以查看临时表。

三.内存表和临时表的区别

	临时表	内存表
存储	表结构和数据都存储在内存中	表结构存储在磁盘中，表数据存储在内存中
会话	单个会话独享的，是会话级别的	可以多个会话共享
引擎	临时表默认，myisam	内存表默认，memory
断开连接	表结构和表数据都没了	表结构和表数据都存在
服务重启	表结构和表数据都没了	表结构存在，表数据不存在
性能	由于表数据都是存放在内存中，所以相对来说，查询速度较快，但是数据的维护较为困难

参考：https://blog.csdn.net/pan_junbiao/article/details/86409064