前言
temporary table 临时表,就是为了提高查询效率 临时创建的一个表,用完就丢,有点像是阅后即焚;
比如有一个很大的记录表,我们需要连续几次去查询一小部分数据,这时候如果每次都直接去记录表中查,会比较耗时;
这时就可以考虑创建一个临时表,然后将需要查询的那一小部分数据保存进去,这样后续的几次查询直接去临时表查,会快一点;
目录
- temporary table 介绍
- temporary table 例子
- temporary table 的删除操作
正文
1. temporary table 介绍
我们在联合查询(join)一些复杂的数据时,如果需要多次访问这些数据,那么就可以考虑将这些复杂数据临时存储起来,供后续的多次查询;
这时候就可以用 temporary table 临时表来实现;
创建临时表有两种方式:
- 一种是跟创建普通表一样,设置好字段及其属性,如下所示:
create temporary table table_name(column1, column2, ...);
- 【推荐】另一种就是基于SQL查询结果进行创建,如下所示:
create temporary table table_name
select * from table1;
这样一来,就会创建一个跟table1
一模一样的空的临时表;
不过实际使用时,不会这样用,因为这样创建的临时表没有意义(我们直接从普通表中就可以查询了,临时表没有存在的意义);
而是将一些比较复杂的联合查询的结果存储起来,如下所示:
create temporary table table_name
(select * from table1 left join table2 on table1.id = table2.userId);
2. temporary table 例子
下面就简单介绍下 如果通过复杂查询来创建临时表,以及如何使用临时表;
第一种创建方式就不介绍了,跟创建普通表一样
假设有如下两张表:s_user, s_pet
现在我们想要查询每个用户都养了几只猫,那么就可以用下面的语句来查询:
select t1.`name` as user_name, t2.`name` as cat_name, count(t2.name) as cat_count from s_user as t1
left join s_cat as t2 on t1.id = t2.user_id
group by t1.id
输出结果如下:
在这里可以清楚的看到哪个用户养了几只猫;
但是每次都用上面的语句进行查询,会有点复杂,这时我们可以将其简化,即将上面的查询结果存储到临时表中;
创建对应临时表的语句如下:
create temporary table table_user_join_cat
(select t1.`name` as user_name, t2.`name` as cat_name, count(*) as cat_count from s_user as t1
left join s_cat as t2 on t1.id = t2.user_id
group by t1.id);
这里有几点需要注意:
-
创建的临时表只供当前的会话Session使用,其他会话无法访问到该临时表,这样就意味着不同的会话,可以创建同名的临时表而互不影响
-
临时表在会话结束或者连接断开时,会自动销毁;也可以通过
drop table
手动进行销毁 -
临时表可以跟已有的普通表重名,重名后访问到的是临时表,重名的普通表无法访问,除非删除临时表;
但是不建议重名,比如连接断开并重连,临时表自动删除,这时候又手动删除临时表,结果把已有的普通表删除了
下面我们就可以直接通过临时表进行查询,语句如下:就是普通的查询语句
select * from table_user_join_cat;
上面的例子,具体实践可以通过Navicat进行实践,开两个不同的命令行窗口,然后测试,如下所示:
- 分别开启两个命令行界面:
- 然后其中一个创建临时表并访问数据,另一个直接访问临时表,效果如下:
也可以通过 Mysql Shell 进行实践,开两个Mysql Shell 终端进行测试,如下所示:
3. temporary table 的删除操作
一般数据库连接断开后,会自动删除临时表;
但是如果用了数据库连接池,那么断开的连接可能会被后面的客户端重用,此时前面客户端创建的临时表就不会被自动删除,因为连接没有关闭;
所以一般都推荐手动删除临时表的操作,语句如下:
drop temporary table table_name;
其中 temporary 关键字是可以省略的,但是不建议省略;
因为加了temporary 关键字就表示,只删除 table_name 表名对应的临时表,普通表不受影响;
但是如果没有 temporary 关键字,则会先去找对应的临时表,如果存在,就只删除临时表,如果临时表不存在,就会删除对应的普通表;
总结
- 临时表多用来存储复杂查询的结果集,用以提高查询效率;
- 临时表创建时,可以基于复杂查询的结果集进行创建;
- 临时表只在创建它的会话中才有效,其他会话无法访问;
- 临时表删除时建议加
temporary
关键字(drop temporary table table_name
),以防止误删除普通表;
评论区