MySQL数据库临时表的应用与实例

按照MySQL临时表的存储位置可以将其分为内存临时表（in-memory）和外存临时表（on-disk）。按照临时表的创建时机可以将其分为自动创建的临时表以及手动创建的临时表。

1.临时表的创建、查看与删除

（1）手动创建临时表。

手动创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字即可。

（2）查看临时表的定义可以使用MySQL语句“SHOW CREATE TABLE 临时表名；”。

（3）断开MySQL服务器的连接，临时表的表结构定义文件以及表记录将被清除。使用DROP命令也可以删除临时表，语法格式如下：

DROP TEMPORARY TABLE 临时表表名；

2.使用临时表的注意事项

使用存储程序可以实现表数据的复杂加工处理，有时需要将SELECT语句的查询结果集临时地保存到存储程序（例如函数、存储过程）的变量中，不过目前MySQL并不支持表类型变量。临时表可以模拟实现表类型变量的功能。使用临时表需要注意以下几点：

（1）临时表如果与基表重名，那么基表将被隐藏，除非删除临时表，基表才能被访问。

（2）Memory、MyISAM、Merge或者InnoDB存储引擎的表都支持临时表。

（3）临时表不支持聚簇索引、触发器。

（4）SHOW TABLES 命令不会显示临时表的信息。

（5）不能用RENAME来重命名一个临时表。但可以使用ALTER TABLE重命名临时表。

（6）在同一条SELECT语句中，临时表只能引用一次。(www.xing528.com)

例如下面的SELECT语句将抛出“ERROR 1137（HY000）：Can't reopen table：'t1'”错误信息。

select * from temp as t1，temp as t2；

3.派生表（Derived Table）

派生表与视图一样，一般在from子句中使用，其语法格式如下：

….from（select子句）派生表名….

派生表必须是一个有效的表，因此它必须遵守以下规则：

（1）每个派生表必须有自己的别名。

（2）派生表中的所有字段必须要有名称，字段名必须唯一。

4.子查询、视图、临时表、派生表的区别

（1）子查询一般在主查询语句中的where子句或者having子句中使用。

（2）视图通常在主查询语句中的from子句中使用。视图本质上是一条select语句，执行的是某一个数据源某个字段的查询操作，如果视图的“主查询”语句是update、delete或者insert语句，且“主查询”语句执行了该数据源该字段的更新操作，那么主查询语句将出错。原因很简单，在对某个表的某个字段操作时，查询操作（select语句）不能与更新操作（update、delete或者insert语句）同时进行。

（3）与视图相似，临时表一般在from子句中使用。临时表与视图的区别在于：临时表本质上也是一条select语句，执行的是某一个数据源某个字段的查询操作，但由于临时表会先执行完毕，并且将查询结果集提前放到服务器内存。因此“临时表”的“主查询”语句（例如：update、delete或者insert语句）执行字段的更新操作时，不会产生“ERROR 1443（HY000）”错误。

（4）派生表与临时表的功能基本相同，它们之间的最大区别在于生命周期不同。临时表如果是手工创建，那么临时表的生命周期在MySQL服务器连接过程中有效；而派生表的生命周期仅在本次select语句执行的过程中有效，本次select语句执行结束，派生表立即清除。因此，如果希望延长查询结果集的生命周期，可以选用临时表；反之亦然。

另外，通过视图虽然可以更新基表的数据，但本书并不建议这样做。原因在于：通过视图更新基表数据，并不会触发触发器的运行。