MySQL中临时表的基本建立与使用教程

当工作在比较大的表上时，你可能偶尔还要运行好多查询获取一个大量数据的小的子集，不是对整个表运行很多查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后在很多表运行查询。

创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字：

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

临时表将在你连接MySQL期间存在。当你断开时，MySQL将手动删除表并传递所用的空间。当然你可以在依旧连接的之后删除表并释放空间。

DROP TABLE tmp_table

如果在你创建名为tmp_table临时表时名为tmp_table的表在中已经存在，临时表将有必要屏蔽（隐藏）非临时表tmp_table。

如果你声明临时表是一个HEAP表，MySQL也禁止你选定在存储中构建它：

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

因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。然而，HEAP表与一般的表有些不同，且有自身的限制。详见MySQL参考指南。

正如上面的建议，你应当检测临时表看看它们是否真的比对大量运行查询快。如果数据很好地索引，临时表可能一点不快。

临时表再断开于mysql的连接后平台会手动删除临时表中的数据，但是这只限于用上面语句构建的表：

定义数组：

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

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

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

另外mysql也允许你在存储中直接建立临时表，因为是在存储中所有速度会迅速，语法如下：

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

从里面的预测可以看出临时表的数据是会被清空的mysql存储过程临时表实例，你断开了联结都会被手动清空，但是你程序中不可能每发行一次sql就连接一次吧(如果是这样的话，那都会发生你害怕的弊端，如果不是就没有问题)，因为只有断开连接才能被清空数据，在一个连接上面发行多次sql的话平台是不会自动清空临时表数据的。

只有在当前连接状况下， TEMPORARY 表才是可见的。当连接关掉时， TEMPORARY 表被手动更改。这意味着两个不同的连接可以使用相似的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。（原有的表被隐藏，直到临时表被更改时为止。）必须拥有 CREATE TEMPORARY TABLES 权限，才能建立临时表。可以借助指定 ENGINE|TYPE = MEMORY; 来选定创建内存临时表。

如果表已存在，则使用关键词 IF NOT EXISTS 可以防范出现出错。注意mysql存储过程临时表实例，原有表的结构与 CREATE TABLE 语句中表示的表的结构能否相同，这一点没有验证。注释：如果在 CREATE TABLE...SELECT 语句中使用 IF NOT EXISTS ，则不论表能否已存在，由 SELECT 部分选取的记录都会被插入。

DROP TEMPORARY TABLE 语句只更改 TEMPORARY 表，语句不会终止正在进行中的事务。在采取连接池的状况下，为严防多次 CREATE 、 DROP TEMPORARY TABLE 带来的性能难题，可以使用 CREATE IF NOT EXISTS + TRUNCATE TABLE 的方法来提高性能。

临时表支持外键、索引指定。在连接非临时表查询可以借助指定字段或键值来提高性能。

CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50),OUT o_counts BIGINT)
BEGIN
     create temporary table if not exists tmpTable – 不存在则创建临时表
     (
      objChk varchar(255) primary key,
      ModelName varchar(50),
      Operator varchar(500),
      PModelName varchar(50)
     );
     truncate TABLE tmpTable; -- 使用前先清空临时表。
 
     insert into tmpTable values(i_chars,i_chars,i_chars,i_chars);
     insert into tmpTable values(i_chars,i_chars,i_chars,i_chars); -- 语句1
     select * from tmpTable; -- 语句2
     select count(*) into o_counts from tmpTable; -- 语句3
END;

上述代码语句 1 返回临时表中所有数据，语句 2 将总记录数读取输出参数。 truncate 语句放到 create 之后，而不是整个存储过程最终，原因在于之后的段落 1 插入相同的值，二临时表 PK 校验将形成一个错误，则传输过程最后异常结束。综合异常处理，可以如下修改，以在每次存储过程调用完毕后剔除临时表。

再来看一个例子：

CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50),OUT o_counts BIGINT)
BEGIN
     create temporary table if not exists tmpTable
     (
      objChk varchar(255) primary key,
      ModelName varchar(50),
      Operator varchar(500),
      PModelName varchar(50)
     ) ENGINE = MEMORY;
     begin
          declare exit handler for sqlwarning,NOT FOUND,SQLEXCEPTION set o_counts=-1;
          insert into tmpTable values(i_chars,i_chars,i_chars,i_chars);
          select * from tmpTable; -- 语句1
          select count(*) into o_counts from tmpTable;
     end;
     truncate TABLE tmpTable; -- 语句2
END;

虽然上述代码语句 2 最后 truncate table 清空了全部临时表数据，但上面语句 1 select 的数据结果集不会被剔除。已借助 java 程序验证。

临时表可以解决二维数组输出的弊端。但是，的数据插入只能由程序采取循环来做。某些特殊状况下的输入字段，例如选取好的一组待删除数据的 ID 的输入，也没法借助循环来做。临时表也不适用于需要三维数组的状况。

本文来自电脑杂谈，转载请注明本文网址：
https://www.pc-fly.com/a/jisuanjixue/article-119985-1.html