在MySQL中有三种虚拟表:临时表、内存表、视图(普通表),针对视图,这个大家都比较熟,但是当数据量非常大时,你可能需要对查询出来的一部分结果进行单独处理,比如在做一些参数比较多的报表系统时,常常会听到这样一句话:把这些参数取出来放到临时表里用。那什么是临时表,什么又是内存表呢?
临时表,顾名思义,就是为了保存临时数据而存在的(MySQL 3.23版本以后才有),临时表只在当前连接可见,当关闭连接时,Mysql会自动删表并释放所有空间。如果你使用Navicat等客户端程工具连接MySQL创建临时表,那么只有在关闭客户端关闭时才会销毁临时表(也可手动销毁)。
//创建临时表 TEMPORARY是关键字
CREATE TEMPORARY TABLE IF NOT EXISTS gu
(
id INT NOT NULL DEFAULT 0,
name VARCHAR(10) NOT NULL
);
//复制的方式创建临时表
CREATE TEMPORARY TABLE gu
AS SELECT * FROM tb_gu;
//手动销毁临时表
DROP TABLE IF EXISTS gu;
使用临时表的注意事项
1.临时表只在当前连接可见,连接关闭的时候,会自动drop。
2.临时表的存储引擎:memor、myisam、merge或innodb,临时表不支持mysql cluster簇。
3.临时表超容会转换成磁盘表。
4.show tables语句不会列出临时表,在information_schema中也不存在临时表信息,要查看临时表:
show create table
5.同一个查询语句,只能用一次临时表,不能将表和自己做连接
SELECT * FROM gu, gu AS t2;
6.重命名临时表,不能用rename 可以alter table替代
ALTER TABLE old_name RENAME new_name;
7.如果临时表表名在磁盘表中已存在,那么临时表会暂时覆盖磁盘表,select 查询,只会显示临时表。
内存表,顾名思义是数据放在内存里面的表(内存表的表结构建立在磁盘中),当MySQL断开当前连接后,临时表的表结构和表数据都drop,但内存表的表结构和表数据都存在。当MySQL服务重启之后,内存表的数据会丢失,但表结构依旧存在。
创建内存表与创建普通表语法一样,但需要将存储引擎设置为:ENGINE = MEMORY
CREATE TABLE IF NOT EXISTS gu (
id INT NOT NULL DEFAULT 0,
name VARCHAR(10) NOT NULL
) ENGINE = MEMORY;
// 删除内存表
DROP TABLE IF EXISTS gu;
使用内存表的注意事项
1.当MySQL服务重启,内存表的数据丢失,结构存在。
2.当前session创建的内存表,对其他session可见,所以不同session,创建内存表的名字不能一样。
3.Mysql安装目录data下只有table名.frm,因为内存表的表结构放在磁盘上,数据放在内存中。
4.可以创建索引,删除索引,支持唯一索引(索引和数据一样保存在内存中)。
5.内存表不能包含BLOB或者TEXT等字段,但是支持AUTO_INCREMENT列。
6.可以通过参数max_heap_table_size来设定内存表大小,到达max_heap_table_size设定的内存上限后将报错。