内存表和临时表的区别_MySQL中临时表的基本创建与使用教程

当处理非常大的表时，您可能偶尔需要运行许多查询来获取大量数据中的一小部分。与对整个表运行这些查询相比，让 MySQL 一次找到您需要的几条记录，将这些记录选入临时表，然后对这些表运行查询可能会更快。

创建临时表很容易，只需在普通的 TABLE 语句中添加以下关键字：

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

只要您连接到 MySQL，临时表就会存在。断开连接时，MySQL 将自动删除表并释放其使用的空间。当然，您可以在仍处于连接状态时删除表并释放空间。

DROP 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 

从上面的分析可以看出临时表中的数据是会被清除的，当你断开连接的时候会自动清除。但是你的程序不可能每次发出SQL语句时都去连接数据库（如果是这样的话，那你担心的问题就会出现，如果不是的话，就不会有问题）。因为只有在断开数据库连接的时候才会清除数据，所以如果你在一个数据库连接中发出多条SQL语句的话，系统不会自动清除临时表数据。

该表仅对当前连接可见。关闭连接时，表将自动删除。这意味着两个不同的连接可以使用相同的临时表名称，而不会相互冲突或与同名的现有非临时表冲突。（现有表将处于隐藏状态，直到临时表被删除。）您必须具有创建临时表的权限。您可以指定 |TYPE = ; 来创建内存中的临时表。

如果表已存在，则使用 IF NOT 关键字可防止出现错误。请注意，没有验证原始表的结构是否与 TABLE 语句中指示的表的结构相同。注意：如果在 TABLE... 语句中使用 IF NOT，则无论表是否已存在，都将插入该部分选择的记录。

DROP TABLE语句只是删除表，并不会终止正在进行的事务。使用连接池时，为防止多次DROP TABLE语句造成性能瓶颈，可以使用IF NOT + 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 将记录总数写入输出参数。该语句放在后面，而不是整个存储过程的末尾，因为后续的语句 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中的表清除了所有的临时表数据，但是上面语句1的数据结果集并不会被清除。这一点已经通过Java程序进行了验证。

临时表可以解决二维数组输出的问题，但是大规模数据的插入只能通过程序循环来完成，一些特殊情况的输入数组，比如输入一组需要删除的数据的ID，也只能通过循环来完成，临时表也不适合需要三维数组的场合。