mysql虚拟表

虚拟表，顾名思义，就是实际上并不存在（物理上不存在），但是逻辑上存在的表。这样说很抽象，还是看一些实际的例子吧。

在mysql中，存在三种虚拟表：临时表、内存表和视图。视图会单独讲，本节仅仅将临时表和内存表。

一、mysql临时表

　　1、什么是临时表

临时表是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。 临时表的数据和表结构都储存在内存之中，退出时，其所占的空间会自动被释放。

　　2、创建临时表

         (1)定义字段

      CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,    value INTEGER NOT NULL)    

     (2)直接将查询结果导入临时表

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

3、查询临时表
select * from tmp_table

4、删除临时表
drop table tmp_table

二、临时表的应用

当工作在十分大的表上运行时，在实际操作中你可能会需要运行很多的相关查询，来获的一个大量数据的小的子集。较好的办法，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表，然后对这些表运行查询。

　　1）、当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。

　　2）、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。

3）、临时表默认的是MyISAM,但是可以修改。

4)、可以把一些经常访问的数据放到临时表中，这样访问时会快一些,因为数据是在服务器内存中,另外每次查询的时候，数据库都需要生成一些临时数据在临时表里

三、临时表使用注意事项：

(1)临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。这就意味着你可以在两个不同的连接里使用相同的临时表名，并且相互不会冲突，或者使用已经存在的表，但不是临时表的表名。(当这个临时表存在的时候，存在的表被隐藏了，如果临时表被drop，存在的表就可见了)。

(2) 临时表只能用在 memory,myisam,merge,或者innodb引擎。

（3）临时表不支持mysql cluster(簇)。

（4）在同一个query语句中，你只能查找一次临时表。例如：下面的就不可用

　　mysql> SELECT * FROM temp_table, temp_table AS t2;

　　ERROR 1137: Can't reopen table: 'temp_table'

　　如果在一个存储函数里，你用不同的别名查找一个临时表多次，或者在这个存储函数里用不同的语句查找，这个错误都会发生。

（5）show tables 语句不会列举临时表，但是会列出内存表。

（6）你不能用rename来重命名一个临时表。但是，你可以alter table代替：

　　mysql>ALTER TABLE orig_name RENAME new_name;

********************************************************************************************

四、内存表

1、内存表:表结构建在磁盘里，数据在内存里 ，当停止服务后，表中的数据丢失，而表的结构不会丢失。内存表也可以被看作是临时表的一种。

2、内存表的建立：

CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL, value INTEGER NOT NULL ) TYPE = HEAP  

注意： TYPE = HEAP必须要有。

五、内存表的应用

内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库。

1、heap对所有用户的连接是可见的，这使得它非常适合做缓存。 
2、一旦服务器重启，所有heap表数据丢失，但是heap表结构仍然存在，因为heap表结构是存放在实际数据库路径下的，不会自动删除。重启之后，heap将被清空，这时候对heap的查询结果都是空的。 
3、如果heap是复制的某数据表（创建普通用户表这些也会丢失），则复制之后所有主键、索引、自增等格式将不复存在，需要重新添加主键和索引，如果需要的话。 
4、对于重启造成的数据丢失，有以下的解决办法： 
　a、在任何查询之前，执行一次简单的查询，判断heap表是否存在数据，如果不存在，则把数据重新写入，或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件，在需要用该heap表的页面随时调用，比较方便。 
　b、对于需要该heap表的页面，在该页面第一次且仅在第一次查询该表时，对数据集结果进行判断，如果结果为空，则需要重新写入数据。这样可以节省一次查询。 
　c、更好的办法是在mysql每次重新启动时自动写入数据到heap，但是需要配置服务器，过程比较复杂，通用性受到限制。

六、内存表的注意事项

1、heap不允许使用xxxTEXT和xxxBLOB数据类型；只允许使用=和<=>操作符来搜索记录（不允 许& amp; lt;、>、<=或>=）；mysql4.1版本之前不支持auto_increment；只允许对非空数据列进行索引（not null）。

注：操作符 “<=>” 说明：NULL-safe equal.这个操作符和“=”操作符执行相同的比较操作，不过在两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。 
2、内存表可以通过max_heap_table_size = 2048M来加大使用的内存。

3、内存表必须使用memory存储引擎