MySQL临时表
MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。
实例
以下展示了使用MySQL 临时表的简单实例，以下的SQL代码可以适用于PHP脚本的mysql_query()函数。
>mysql> CREATE TEMPORARY TABLE SalesSummary ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO SalesSummary -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2);mysql> SELECT * FROM SalesSummary;+--------------+-------------+----------------+------------------+| product_name | total_sales | avg_unit_price | total_units_sold |+--------------+-------------+----------------+------------------+| cucumber | 100.25 | 90.00 | 2 |+--------------+-------------+----------------+------------------+1 row in set (0.00 sec)
当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。
如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。
删除MySQL 临时表
默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
以下是手动删除临时表的实例：
mysql> CREATE TEMPORARY TABLE SalesSummary ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO SalesSummary -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2);mysql> SELECT * FROM SalesSummary;+--------------+-------------+----------------+------------------+| product_name | total_sales | avg_unit_price | total_units_sold |+--------------+-------------+----------------+------------------+| cucumber | 100.25 | 90.00 | 2 |+--------------+-------------+----------------+------------------+1 row in set (0.00 sec)mysql> DROP TABLE SalesSummary;mysql> SELECT * FROM SalesSummary;ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist
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
从上面的分析可以看出临时表的数据是会被清空的，你断开了连接就会被自动清空，但是你程序中不可能每发行一次sql就连接一次数据库吧(如果是这样的话，那就会出现你担心的问题，如果不是就没有问题)，因为只有断开数据库连接才会被清空数据，在一个数据库连接里面发行多次sql的话系统是不会自动清空临时表数据的。
只有在当前连接情况下， TEMPORARY 表才是可见的。当连接关闭时， TEMPORARY 表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。（原有的表被隐藏，直到临时表被取消时为止。）必须拥有 CREATE TEMPORARY TABLES 权限，才能创建临时表。可以通过指定 ENGINE|TYPE = MEMORY; 来指定创建内存临时表。
如果表已存在，则使用关键词 IF NOT EXISTS 可以防止发生错误。注意，原有表的结构与 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; -- 语句3END;
上述代码语句 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; -- 语句2END;
虽然上述代码语句 2 最后 truncate table 清空了全部临时表数据，但前面语句 1 select 的数据结果集不会被清除。已通过 java 程序验证。
临时表可以解决二维数组输出的问题。但是，大批量的数据插入只能由程序采用循环来做。某些特殊情况下的输入数组，例如选择好的一组待删除数据的 ID 的输入，也只能利用循环来做。临时表也不适用于需要三维数组的情况。
通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。
内部临时表
内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。
内部临时表有两种类型：一种是HEAP临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。另一种是OnDisk临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE（详情请参考MySQL手册中系统变量部分），HEAP临时表将会被自动转换成OnDisk临时表。OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。
mysql的内存表和临时表
内存表：
session 1
$ mysql -uroot
root@(none) 10:05:06>use test
Database changed
root@test 10:06:06>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
root@test 10:08:46>insert into tmp_memory values (1);
Query OK, 1 row affected (0.00 sec)
root@test 10:08:46>
session2
$ mysql -uroot test
root@test 10:05:12>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;
ERROR 1050 (42S01): Table 'tmp_memory' already exists
root@test 10:16:27>select * from tmp_memory;
+------+
| i |
+------+
| 1 |
+------+
row in set (0.00 sec)
1. 多个session，创建表的名字不能一样
2. 一个session创建会话后，对其他session也是可见的
3. data目录下只有tmp_memory.frm ,表结构放在磁盘上，数据放在内存中
4. mysql 重启或者关闭后内存表里的数据会丢失，但是表结构仍然存在
5. 可以创建索引,删除索引,支持唯一索引
6. 不影响主备，主库上插入的数据，备库也可以查到
7. show tables 看得到表
临时表：
session1
$ mysql -uroot test
root@test 10:30:18>CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);
Query OK, 0 rows affected (0.05 sec)
root@test 10:31:54>select * from tmp_table;
+--------+-------+
| name | value |
+--------+-------+
| aaaaaa | 10 |
+--------+-------+
row in set (0.00 sec)
session2
root@test 10:20:13> CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);
Query OK, 0 rows affected (0.02 sec)
root@test 10:30:39>insert into tmp_table values ('bbbbbbb',10);
Query OK, 1 row affected (0.01 sec)
root@test 10:31:33>select * from tmp_table;
+---------+-------+
| name | value |
+---------+-------+
| bbbbbbb | 10 |
+---------+-------+
row in set (0.00 sec)
root@test 10:31:43>exit
Bye
[1 Single:MS-Master db152011.sqa.cm6: mysql ~ ]
$ mysql -uroot test
root@test 10:32:17>select * from tmp_table;
ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist
root@test 10:32:22>
root@test 10:32:23>
1. 创建的表的名字可以一样
2. 表结构和数据都放在内存中
3. 会话消失表结构和数据都消失
4. 可以创建索引,删除索引
5. 主库创建的表，备库查不到，
6. show tables 看不到表
使用内存表需要注意的事项
1. 内存表需要自己delete数据或者drop表；需要drop权限，这点比较危险
2. 内存表的表结构是保存在磁盘上的，如果多个session使用同一个表名，会存在冲突；如果不需要使用表名，如果使用一次都需要创建表结构，到时候会有很多小文件存在，不利于db的维护，dba清理表也有风险；
基于以上不适合用内存表
1. 临时表是会话级别的，即使多个session创建的表名一样，都相互不影响
2. 会话消失，所有的都消失，这点很不利于应用排查问题
另外这两个都需要消耗额外的内存空间，虽然db端可以忍受，但是不太可控；DB端还有这个参数:
max_tmp_tables 一个客户能同时保持打开的临时表的最大数量，这个值默认32，可以根据需要调整此值
更多相关文章
- 2月2日APAC数据科学研讨会：使用 Oracle Accelerated Data Science
- Oracle 11gR2新特性--延迟段创建（Deferred Segment Creation）和exp
- 【Linux】shell脚本实战-if多分支条件语句详解
- 【DB笔试面试561】在Oracle中，如何预估即将创建索引的大小？
- 【Linux】shell脚本实战-流程控制语句case
- 指针创建STRING函数
- Django开发中常用的命令总结
- Oracle Directory（目录）介绍
- Linux下通过受限bash创建指定权限的账号