MySQL 必知必会
朱晓峰
前摩根大通银行技术部副总裁、系统架构师
17746 人已学习
新⼈⾸单¥59
登录后,你可以任选4讲全文学习
课程目录
已完结/共 36 讲
MySQL 必知必会
15
15
1.0x
00:00/00:00
登录|注册

13 | 临时表:复杂查询,如何保存中间结果?

你好,我是朱晓峰。今天,我来和你聊一聊临时表。
当我们遇到一些复杂查询的时候,经常无法一步到位,或者是一步到位会导致查询语句太过复杂,开发和维护的成本过高。这个时候,就可以使用临时表。
下面,我就结合实际的项目来讲解一下,怎么拆解一个复杂的查询,通过临时表来保存中间结果,从而把一个复杂查询变得简单而且容易实现。

临时表是什么?

临时表是一种特殊的表,用来存储查询的中间结果,并且会随着当前连接的结束而自动删除。MySQL 中有 2 种临时表,分别是内部临时表和外部临时表
内部临时表主要用于性能优化,由系统自动产生,我们无法看到;
外部临时表通过 SQL 语句创建,我们可以使用。
因为我们不能使用内部临时表,所以我就不多讲了。今天,我来重点讲一讲我们可以创建和使用的外部临时表。
首先,你要知道临时表的创建语法结构:
CREATE TEMPORARY TABLE 表名
(
字段名 字段类型,
...
);
跟普通表相比,临时表有 3 个不同的特征:
临时表的创建语法需要用到关键字 TEMPORARY;
临时表创建完成之后,只有当前连接可见,其他连接是看不到的,具有连接隔离性;
临时表在当前连接结束之后,会被自动删除。
因为临时表有连接隔离性,不同连接创建相同名称的临时表也不会产生冲突,适合并发程序的运行。而且,连接结束之后,临时表会自动删除,也不用担心大量无用的中间数据会残留在数据库中。因此,我们就可以利用这些特点,用临时表来存储 SQL 查询的中间结果。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

临时表在复杂查询中的应用是数据库开发中的重要技术。本文介绍了临时表的概念和创建方法,并通过实际案例演示了如何利用临时表简化复杂查询。作者以超市经营数据为例,展示了如何使用临时表分别存储销售、进货和返厂数据,并最终将这些数据汇总到一起。通过临时表,可以将复杂的查询拆分成多个步骤,存储中间结果,从而降低查询难度。这种面向过程的编程模式大大简化了复杂查询的实现。文章深入浅出地介绍了临时表的使用方法,对于需要处理复杂查询的数据库开发人员具有很高的实用价值。 临时表可分为内存临时表和磁盘临时表,它们各有优缺点。内存临时表查询速度更快,但数据一旦断电就会消失;而磁盘临时表则相对稳定。通过指定引擎类型,可以告诉MySQL临时表存储在内存中,以加快存取速度。 总结来说,临时表的使用有很多好处,可以帮助简化复杂的SQL查询,而且连接隔离,不同的连接可以使用相同的临时表名称,相互之间不会受到影响。临时表在连接结束时自动删除,不会占用磁盘空间。然而,也需要综合考虑简化查询和挤占资源两个方面,避免过度加重系统负担。 思考题是关于如何查询每个门店、每台收款机的销售金额占所属门店的销售金额的比率,欢迎读者在留言区写下思考和答案,展开交流讨论。 总的来说,本文通过介绍临时表的概念和应用,为数据库开发人员提供了有价值的技术指导和实践案例。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 必知必会》
新⼈⾸单¥59
立即购买
登录 后留言

全部留言(15)

  • 最新
  • 精选
  • 朱晓峰
    置顶
    你好,我是朱晓峰,下面我就来公布一下上节课思考题的答案: 上节课,我们学习了事务。下面是思考题的答案: 这种说法是不对的,事务会确保事务处理中的操作要么全部执行,要么全部不执行,执行中遇到错误,是继续还是回滚,则需要程序员来处理。
    2021-04-21
    4
  • undefined
    据了解,临时表的开销很大,不建议在高访问量的线上系统中使用。离线备份库或供数据分析所用的数据库上可以考虑有限制的使用。

    作者回复: 临时表的执行效率比较低,数据量大的话,可以考虑其他的方式,比如把中间结果保存在数组中

    2021-04-06
    2
    11
  • 对于连接池,连接使用完不会销毁,使用完后会放到连接池中。那使用该连接创建临时表是没有被销毁,会不会影响后面再次从连接池取出该连接使用的情况?

    作者回复: 只要连接关闭,临时表就会被删除。

    2021-04-18
    4
  • giteebravo
    最后左连接的代码中,为什么要使用 having 而不使用 where 呢?

    作者回复: 如果用WHERE,会提示筛选条件中的字段不存在。而HAVING是生成结果集后进行筛选,所以可以用重命名之后的字段名进行筛选

    2021-04-06
    6
    4
  • 星空下
    数据库一般是性能瓶颈点,用临时表太占用数据库资源吧

    作者回复: 如果是小项目,数据库资源充足,可以使用

    2021-04-06
    2
  • 洛奇
    临时表的数据是不是易丢失,这不重要吧?断电后,连接也断了,这时候有去找回临时表的数据的必要吗?

    作者回复: 是的,断电的话连接中断,无法找回数据了。主要问题还是占用内存空间。

    2021-04-06
    2
  • giteebravo
    当引擎类型为 memory 时,如果去掉 temporary 那么表还会存储在内存中吗?

    作者回复: 是的,会存储在内存中。

    2021-04-06
    2
    1
  • 豆豆酱
    临时表不是有内存和磁盘两种么?对于磁盘,应该是永久储存吧,那么连接断开的话,磁盘的临时表也会被删除么?

    作者回复: mysql会监控存储在磁盘中的临时表,如果连接断开,这些临时表会被清除,存储空间会被释放

    2022-04-26归属地:北京
    2
  • Geek_8e65ed
    🐂🍺

    作者回复: ☺

    2021-11-03
  • 彭彬
    请问老师:数据量大时,临时表能否建立索引?

    作者回复: 可以的

    2021-09-22
收起评论
显示
设置
留言
15
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部