MySQL临时表与内存表

在MySQL中有三种虚拟表：临时表、内存表、视图。下面简单介绍一下临时表和内存表的使用。

1、临时表

MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在MySQL 3.23版本中添加。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

1.1 创建临时表

在MySQL中创建临时表使用CREATE TEMPORARY TABLE语句，其语法格式如下：

CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名
(
	...
);

示例：创建临时表。

CREATE TEMPORARY TABLE IF NOT EXISTS temp_user
(
	id INT NOT NULL DEFAULT 0,
	name VARCHAR(10) NOT NULL
);

还可以通过复制表的方式来创建临时表。

示例：通过复制表的方式来创建临时表。

CREATE TEMPORARY TABLE temp_user
AS SELECT * FROM tb_user;

1.2 删除临时表

默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话中手动删除临时表。删除临时表与删除普通表的语句是一样的，使用 DROP TABLE语句。

示例：删除临时表。

DROP TABLE IF EXISTS temp_user;

1.3 使用临时表的注意事项

（1）临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。比如打开mysql 就是一个连接会话。两个不同的连接可以使用相同名字的临时表，两个表之间不存在什么关系，如果临时表的名字和已经存在的磁盘表名字一样，那么临时表会暂时覆盖磁盘表。就是说，你select 查询，只会显示临时表里面的，不会显示磁盘表。

（2）临时表的存储引擎：memor，myisam，merge，innodb，临时表不支持mysql cluster簇。

（3）同一个查询语句，只能用一次临时表，就是说不能将表和自己做连接等。

（4）重命名表，不能用rename 可以用alter table代替。

（5）如果超出了临时表的容量，临时表会转换成磁盘表。

（6）show tables语句不会列出临时表，在information_schema中也不存在临时表信息，show create table可以查看临时表。

 

2、内存表

内存表的表结构建立在磁盘里面，数据放在内存里面；

当MySQL断开当前连接后，临时表的表结构和表数据都没了，但内存表的表结构和表数据都存在；

当MySQL服务重启之后，内存表的数据会丢失，但表结构依旧存。

2.1 创建内存表

创建内存表与创建普通表一样，使用CREATE TABLE语句，但需要将存储引擎设置为：ENGINE = MEMORY。其语法格式如下：

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

示例：创建内存表。

CREATE TABLE IF NOT EXISTS tmp_table (
	id INT NOT NULL DEFAULT 0,
	name VARCHAR(10) NOT NULL
) ENGINE = MEMORY; 

2.2 删除内存表

删除内存表与删除普通表的语句是一样的，使用 DROP TABLE语句。

示例：删除内存表。

DROP TABLE IF EXISTS tmp_table;

2.3 使用内存表的注意事项

（1）当MySQL服务重启之后，内存表的数据会丢失，表结构依旧存。

（2）多个session，创建表的名字不能一样。

（3）一个session创建会话后，对其他session也是可见的。

（4）data目录下只有tmp_memory.frm，表结构放在磁盘上，数据放在内存中。

（5）可以创建索引，删除索引，支持唯一索引。

（6）不影响主备，主库上插入的数据，备库也可以查到。

（7）show tables 语句可以查看得到表。

（8）内存表不能包含BLOB或者TEXT列。

（9）内存表支持AUTO_INCREMENT列。

 

3、临时表与内存表区别

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