MySQL临时表

1 临时表简介

    通常情况下我们创建的表具有一个较长的生命周期，它使用CREATE TABLE语句创建，我们也称之为持久表。而有时候我们需要临时表，来临时存储复杂的select语句结果。

    临时表的生命周期较短，而且只能对创建它的session（当前连接）可见，当session退出时临时表被删除，也可使用drop table语句删除。在两个不同的链接里使用相同的临时表名不会产生冲突，它们只对当前session有效。

    临时表的名字可以和一个已有的持久表的名字相同，持久表不会被删除，但会被当前session的临时表隐藏。

    临时表和内存表都是数据存放在内存中（临时表表结构也在内存中，内存表表结构在磁盘上），临时表最大所需内存需要通过tmp_table_size = 1024M设定，内存表最大所需内存需要通过max_heap_table_size=1024M设定。当数据超过临时表的最大值设定时，自动转为基于磁盘的MyISAM表，存储在指定的tmpdir目录，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，会提示数据满错误。

2 临时表创建

    2.1 创建语法

CREATE TEMPORARY TABLE ...

    2.2 例如：

mysql> create temporary table tmptable (c1 int, c2 int);

    当创建临时表后，会在操作系统/tmp文件夹下生成类似临时文件

[root@localhost tmp]# ls
#sqla5a_3_2.frm    

3  临时表操作

    3.1 插入数据

mysql> insert into tmptable values(1,2);
mysql> select * from tmptable;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
+------+------+

    3.2 和持久表的对比

    在当前临时表所在的数据库下创建持久表

mysql> create table tmptable (c1 int);
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| tmptable        |
+-----------------+
mysql> insert into tmptable values(1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

    持久表创建成功，但被临时表隐藏，只显示一个表。
4 临时表删除

mysql> drop table tmptable;
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| tmptable        |
+-----------------+
mysql> insert into tmptable values(1);
mysql> select * from tmptable;
+------+
| c1   |
+------+
|    1 |
+------+

    临时表删除后持久表显示表现出来，并可对其操作。

5 临时表使用限制

    5.1 同一个query中，临时表只能被打开一次（持久表可以被多次打开），对比如下：

    持久表

mysql> select * from tmptable;
+------+
| c1   |
+------+
|    1 |
+------+
mysql> select * from tmptable, tmptable AS t2;
+------+------+
| c1   | c1   |
+------+------+
|    1 |    1 |
+------+------+

    临时表

mysql> select * from tmptable;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
+------+------+
mysql> select * from tmptable, tmptable AS t2;
ERROR 1137 (HY000): Can't reopen table: 'tmptable'

**********************************************************************************************
 转载请注明出处：http://blog.csdn.net/jesseyoung/article/details/37809849
**********************************************************************************************