侧边栏壁纸
  • 累计撰写 94 篇文章
  • 累计创建 100 个标签
  • 累计收到 10 条评论

目 录CONTENT

文章目录

MySQL-临时表

汤圆学Java
2023-02-08 / 0 评论 / 0 点赞 / 236 阅读 / 1,443 字
温馨提示:
本文最后更新于 2023-02-08,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

前言

temporary table 临时表,就是为了提高查询效率 临时创建的一个表,用完就丢,有点像是阅后即焚;

比如有一个很大的记录表,我们需要连续几次去查询一小部分数据,这时候如果每次都直接去记录表中查,会比较耗时;

这时就可以考虑创建一个临时表,然后将需要查询的那一小部分数据保存进去,这样后续的几次查询直接去临时表查,会快一点;

目录

  1. temporary table 介绍
  2. temporary table 例子
  3. 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

image-20220523105258223

现在我们想要查询每个用户都养了几只猫,那么就可以用下面的语句来查询:

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

group by 的相关介绍可以参考

输出结果如下:

image-20220523105311246

在这里可以清楚的看到哪个用户养了几只猫;

但是每次都用上面的语句进行查询,会有点复杂,这时我们可以将其简化,即将上面的查询结果存储到临时表中;

创建对应临时表的语句如下:

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进行实践,开两个不同的命令行窗口,然后测试,如下所示:

  • 分别开启两个命令行界面:

image-20220523110348285

  • 然后其中一个创建临时表并访问数据,另一个直接访问临时表,效果如下:

image-20220523111138003

也可以通过 Mysql Shell 进行实践,开两个Mysql Shell 终端进行测试,如下所示:

image-20220523110939507

3. temporary table 的删除操作

一般数据库连接断开后,会自动删除临时表;

但是如果用了数据库连接池,那么断开的连接可能会被后面的客户端重用,此时前面客户端创建的临时表就不会被自动删除,因为连接没有关闭;

所以一般都推荐手动删除临时表的操作,语句如下:

drop temporary table table_name;

其中 temporary 关键字是可以省略的,但是不建议省略;

因为加了temporary 关键字就表示,只删除 table_name 表名对应的临时表,普通表不受影响;

但是如果没有 temporary 关键字,则会先去找对应的临时表,如果存在,就只删除临时表,如果临时表不存在,就会删除对应的普通表;

总结

  1. 临时表多用来存储复杂查询的结果集,用以提高查询效率;
  2. 临时表创建时,可以基于复杂查询的结果集进行创建;
  3. 临时表只在创建它的会话中才有效,其他会话无法访问;
  4. 临时表删除时建议加temporary关键字(drop temporary table table_name),以防止误删除普通表;
0

评论区