mysql临时表

工作中有时候需要查询一些特殊的数据，尤其一个大表里的个别字段的数值，可以讲这些字段放到临时表，查询会更快些。

一、关于临时表常见的操作：

1.1、创建临时表：和正常表很像，只是要加上TEMPORARY  TABLE关键字

 mysql> create temporary table test(id int, name VARCHAR(32));

1.2、插入数据：

mysql> insert into test values(1,'testuser1');

1.3、创建临时表，同时将要查询的记录插入临时表：

mysql> CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name;

1.4、查询数据：

mysql>select *from tmp_table ;

1.5、临时表支持FOREIGN KEY 

  create temporary table test(id int, name VARCHAR(32) , PRIMARY KEY (ID) );#临时表中指定了 FOREIGN KEY 约束，创建成功

  insert into test values(1,'testuser1');   /*插入成功*/

  insert into test values(1,'testuser2');   /*当插入主键字段重复的记录，提示Duplicate entry '1' for key 'PRIMARY'*/

1.6、删除表

 drop table test; /* 删除成功，提示Command processed. No rows were affected*/

1.7、结束会话，在新的会话重新查询

mysql>select *from tmp_table ;

Table 'test' doesn't exist

在session结束后，再执行查询，发现表不存在了，系统在结束会话后自动清除临时表。

二、临时表和正常表相关问题

2.1、同时存在名称相同的临时表，临时表操作的优先级更高

在同一个会话先创建临时表tmp，insert记录；新建同名的正常表tmp，insert记录。查询发现，insert操作的2条记录都在一个表中，说明都作用在一个表了。为了确定是在临时表还是正常表，关掉会话，再打开一个新的会话执行查询，结果为零，说明insert都是在临时表。

新建临时表:create temporary table tmp(id int, name varchar(50));

insert into tmp values(1, "lucy");

新建正常表:create table tmp(id int, name varchar(50));

 insert into tmp values(2, "tom");

 

查询：select * from tmp; 

+------+-------+
| id1  | data1 |
+------+-------+
|    1 | lucy  |
|    2 | tom  |
+------+-------+

说明两次insert 都是作用在同一个表中了。为了确定这个表是临时表还是正常表，关掉会话。然后再打开一个新的会话执行查询：

select * from tmp;

[SELECT - 0 row(s), 0.003 secs]  Empty result set fetched

这说明两次insert操作都是作用于temporary table。

2.2、临时表拥有较高的优先级和先后创建表的顺序无关

为了验证临时表拥有较高的优先级和先后创建表的顺序无关，这次先创建正常表，再创建临时表：

a、先新建正常表:create table tmp(id int, name varchar(50));

 insert into tmp values(2, "tom");

  select * from tmp; 

+------+-------+
| id1  | data1 |
+------+-------+
|    2 | tom  |
+------+-------+

这时只有一个正常表，肯定只能插入正常表

b、新建临时表:create temporary table tmp(id int, name varchar(50));

insert into tmp values(1, "lucy");

查询：select * from tmp; 

+------+-------+
| id1  | data1 |
+------+-------+
|    1 | lucy  |

说明在两个表同时存在时，insert 只作用在一个表中，且这个表是临时表，如果是正常表，就应该显示两条数据了。

这就说明同时存在两个同名的临时表和正常表，insert、select操作temporary table拥有高优先级。

进一步验证drop、delete、update等操作，在临时表和正常表同时存在时，临时表都是优先被操作的。

3、同名正常表不能同时存在，同名临时表也不能共存

都会提示Table 'tmp' already exists

4、同名临时表和注册表允许同时存在

上述操作temporary table和normal tbale 允许同时存在。

5、temporary table与normal table为何允许同时存在

在网上的帖子看到的各高手根据mysql源码验证的了，临时表和正常表是存储在不同的位置的，所以允许共存。

temporary table保存在thd->temporary_table队列中，而normal table是保存在全局的队列中的，这样同名的normal table与temporary table就可以共存。

并且，temporary table是相对于session的，因为session结束后相应的线程就被回收了，那么对应于该线程的temporary table也就被释放了。更进一步，从temporarytable的命名规则我们可以看到，每个temporary table都对应着独特的客户端线程id，那么显然各个Client之间同名的temporary table是允许共存的。

而normal table显然是在任何情况下都不允许的。

三、主从模式下的临时表和正常表