Mysql 临时表

Mysql 临时表

之前对oracle的临时表和内存表有过一篇探索文档，现在对MySQL的临时表进行一些探索。（本文提到的MySQL临时表均为外部临时表）

临时表

临时表是建立在系统临时文件夹中的表。临时表的数据和表结构都存储在内存之中，退出的时候所占的空间会被释放。

语法

//创建临时表tmp_table
CREATE TEMPORARY TABLE tmp_table (
	NAME VARCHAR (10) NOT NULL,
	VALUE1 INTEGER NOT NULL
);

//插入数据
INSERT INTO tmp_table VALUES ('赵','1');

//查询数据
select * from tmp_table;

//删除数据
delete from tmp_table;

//删除表
drop table tmp_table;

应用场景

在程序对数据量很大的表有较频繁的读取操作，每次都多表关联进行查询效率不高。这时候可以使用临时表，先查询出需要的子集放入临时表中，在处理中读取临时表的数据进行处理。

特性

生命周期

MySQL的临时表是会话级的。不同的会话中的临时表彼此互不可见，因此可以创建同名的临时表。

创建临时表，插入并查询数据。

//创建临时表
CREATE TEMPORARY TABLE tmp_table (
	NAME VARCHAR (10) NOT NULL,
	VALUE1 INTEGER NOT NULL
)ENGINE=MEMORY;
//插入数据
INSERT INTO tmp_table VALUES ('赵','1');
//当前会话查询数据
select * from tmp_table;

新开一个查询窗口，查询数据，可见新的会话并不能看到这个临时表。

//新建会话中查询数据库
select * from tmp_table;

在新的会话中建同名的临时表，插入并查询数据，可见互相并不冲突。

//创建同名的临时表
CREATE TEMPORARY TABLE tmp_table (
	NAME VARCHAR (10) NOT NULL,
	VALUE2 INTEGER NOT NULL
);
//插入数据
INSERT INTO tmp_table VALUES ('钱','2');
//新的会话中查询数据
select * from tmp_table;

回到原会话中，查询数据

//原会话查询数据
select * from tmp_table;

在会话分别关闭后，这两个临时表的数据和表结构均会被删除。

屏蔽同名表

在会话中，可以创建和已存在的表同名的临时表。创建后在此会话中原表将会被屏蔽，对该表名的所有操作均指向临时表，直到临时表被删除或者会话结束。

创建表，插入并查询数据

//创建表tmp_table
CREATE TABLE tmp_table (
	NAME VARCHAR (10) NOT NULL,
	VALUE1 INTEGER NOT NULL
);
//插入数据
INSERT INTO tmp_table VALUES ('孙','3');
//查询数据
select * from tmp_table;

创建同名临时表，插入数据并查询，可见此时插入和查询的对象均是临时表。

//创建表临时表tmp_table
CREATE TEMPORARY TABLE tmp_table (
	NAME VARCHAR (10) NOT NULL,
	VALUE1 INTEGER NOT NULL
);
//插入数据
INSERT INTO tmp_table VALUES ('李','4');
//查询数据
select * from tmp_table;

删除数据，删除临时表，再进行查询，可见原表的数据并没有被污染。

//删除数据
delete from tmp_table;
//删除临时表
drop table tmp_table;
//查询数据
select * from tmp_table;

临时表不能自连接

同一个查询语句，只能用一次临时表。

//自连接查询数据
SELECT
	*
FROM
	tmp_table t1,
	tmp_table t2
WHERE
	t1. NAME = t2. NAME;

自连接会报错：[Err] 1137 - Can’t reopen table: XXX

临时表‘不可见’

使用查询表的语句不能查询到临时表。

//查询表
show tables;
show table status;

引擎类型

临时表的引擎只能是：memory（heap）、myisam、merge、innodb ，不支持mysql cluster(簇)。

视图和临时表的区别

1. 视图只是一条预编译的SQL语句，并不保存实际数据；
   临时表是保存在tempdb中的实际的表。
2. 物理空间的分配不一样
   视图不分配空间；
   临时表会分配空间。
3. 视图是一个快照,是一个虚表；
   临时表是客观存在的表类型对象。