CTE (公用表表达式) vs MySQL 临时表先分清两个对象CTEWITH xxx AS (select ...)公用表表达式临时表CREATE TEMPORARY TABLE tmp_xxx1 一句话核心区别CTE 是 SQL 语法层面的结果集不是物理存储临时表是实实在在内存 / 磁盘的表对象。⚠️MySQL 注意MySQL8.0 才支持 CTEWITH 语法5.7 没有 CTE。2 生命周期 作用域重点✅ CTE 公用表表达式 (WITH)WITH cte_user AS (SELECT * FROM user WHERE id100) SELECT * FROM cte_user;生命周期仅当前这一条 SQL 语句内有效语句执行完直接消失。作用域只属于本条 SQL外面拿不到别的 SQL 不能再引用这个 cte_user。不能后续再单独写select * from cte_user;离开这条 with 所在语句就不存在。CTE 不会落地优化器可以选择展开子查询、或者生成内部临时表MySQL 底层有可能自动生成内部临时表但对你是透明的。递归 CTEWITH RECURSIVE✅会话临时表 TEMPORARY TABLECREATE TEMPORARY TABLE tmp_user AS SELECT * FROM user WHERE id100; SELECT * FROM tmp_user; -- 还可以继续执行别的SQL继续访问 tmp_user SELECT count(*) FROM tmp_user;生命周期整个 session 会话有效只要连接不断后续多条 SQL 都可以反复读写这张临时表。可以多次查询、update、delete多条语句复用存储可以存内存或者磁盘支持建索引可以加索引这是很大优势。会话断开才消失或者手动 drop。3 关键对比表对比项CTEWITHCREATE TEMPORARY TABLE 会话临时表存储形式逻辑结果集不强制落地由优化器决定不能建索引真实表内存 / 磁盘存储支持建立索引生命周期单条 SQL 语句结束即失效多条语句不能复用整个 session 会话多条 SQL 反复读写读写能力只能读不能 update/delete CTE 本身支持 select /insert/update /delete多次引用同一条 SQL 内可以多次引用跨 SQL 不行跨多条 SQL 语句反复访问事务回滚跟随 SQL 执行没有实体不存在回滚实体对象数据可以参与事务支持 commit/rollback是否占用 tmpdir 磁盘不一定优化器选择可能底层生成内部临时表会占用 tmpdir (磁盘) 或者内存4 适用场景选型什么时候优先 CTEWITH只在同一条 SQL 内部复用子查询简化 SQL提升可读性需要递归查询递归 CTE树形层级查询不想产生实体对象用完立刻消失缺点无法加索引不能跨语句复用如果 cte 结果集很大性能不一定好。什么时候优先会话临时表 TEMPORARY TABLE后续多条 SQL 都要反复使用这一份中间结果中间结果集很大希望建立索引加速后续关联查询需要对中间结果做 update、delete 修改调试可以分步执行select 看中间结果内容。5 ⚠️常见误区1. ❌误区CTE 一定会生成临时表落地磁盘。MySQL 优化器可以直接把 CTE 展开成子查询不落地只有优化器判断必要的时候底层才生成内部临时表隐式这是 MySQL 内部行为你不能给它建索引。2. ❌误区CTE 临时表。CTE 只是语法糖会话临时表是会话对象生命周期完全不一样。3. ❌误区CTE 可以跨 SQL 复用。写完 with 的那条 select 跑完之后 cte 名字直接失效下一条 select 再查 cte 名字直接报表不存在。6 补充区分另外一个内部临时表group by /union/ CTE 底层优化自动产生的内部临时表也是语句结束销毁用户不可操作不能建索引。简短总结CTE (WITH) 属于 SQL 语法层面的中间结果集生命周期仅限单条 SQL不支持索引会话临时表是会话级别的真实表对象连接不断就一直存在多条 SQL 复用支持索引增删改CTE 底层有可能由 MySQL 自动生成内部临时表但用户无法干预索引。视图 (View) vs 会话临时表 (TEMPORARY TABLE)一句话总结视图是一条保存起来的 SELECT 语句逻辑对象不存数据临时表是真实的表保存实实在在的数据会话级别存储。1. 视图 CREATE VIEWCREATE VIEW v_user AS SELECT id,name FROM user WHERE status1; -- 使用视图像查表一样 SELECT * FROM v_user;✅本质保存查询 SQL不存储数据。每次访问视图都会执行底层的 select 去查原始基表。存储只存视图定义SQL 文本数据来源于原表磁盘只存元数据。生命周期永久对象保存在库里面会话关闭依然存在重启 MySQL 还在需要手动 drop view 删除。作用域所有会话都可以访问有权限前提下全局可见。索引视图本身不能建索引MySQL 普通视图无数据不能建索引物化视图 MySQL 原生不支持DML部分简单视图可以做 update/insert有条件复杂聚合视图不可写。事务本身不持有数据读写最终落到基表。2. 会话临时表 CREATE TEMPORARY TABLECREATE TEMPORARY TABLE tmp_user AS SELECT id,name FROM user WHERE status1; SELECT * FROM tmp_user;✅本质真实表执行完就把结果物理复制存下来内存 / 磁盘 tmpdir数据独立于原表。存储实实在在保存一份中间结果数据。生命周期当前 session 会话连接断开自动销毁MySQL 重启消失。作用域仅当前会话可见其他会话看不到。索引✅支持创建索引可以给临时表建索引优化查询。DML完整支持 select /insert/update /delete。数据独立原表数据后续更新临时表里的数据不会跟着变快照一样。对比表格对比项View 视图CREATE TEMPORARY TABLE 会话临时表本质存储 SQL 定义不存数据真实表保存一份独立中间结果数据生命周期永久存库手动 drop 删除会话级别连接断开自动删除可见范围有权限所有会话均可访问仅当前 session 可见隔离数据来源每次访问实时查询基表原表变化视图结果跟着变生成时快照一份原表更新临时表数据不受影响索引不能建索引✅支持建立索引DML 读写简单视图支持 DML聚合视图不可写完整支持增删改查元数据保存存进 mysql 系统表show tables 可以看到show tables 看不到重启 mysql 之后视图还存在全部消失✅选型场景什么时候用视图 View需要封装复杂查询逻辑每次都实时读取最新基表数据多个会话都需要复用这套查询逻辑对外做访问屏蔽简化业务、权限屏蔽字段希望基表更新之后查询结果自动拿到最新数据。缺点每次访问重新跑底层 SQL中间结果无法加索引不能保存快照。什么时候用会话临时表 TEMPORARY TABLE需要拿到一份快照中间结果后续多次反复查询不想重复跑大 SQL需要对中间结果建立索引加速后续 join只在当前这一次会话里面使用用完连接关闭自动清理不用手动删对象希望原表的数据变化不影响这份中间结果。缺点数据是快照不会自动同步原表别的会话访问不到连接池复用会话会残留旧临时表。⚠️高频坑点❌误区视图存了一份结果✅视图只是存 SQL每次访问重新执行。❌误区临时表的数据会跟随原表自动更新✅生成之后就是独立快照原表改了它不变。❌误区临时表 show tables 可以查到✅查不到临时会话表。MySQL没有原生物化视图如果想要物化视图的效果业务上经常手动用CREATE TEMPORARY TABLE ... AS SELECT模拟物化视图。补充回顾帮你串一遍CTE(WITH)单条 SQL 内有效语法层面中间结果内部临时表MySQL 优化器自动生成语句结束销毁TEMPORARY 会话临时表会话级真实表View 视图永久逻辑对象存 SQL实时查基表简单总结视图是保存的查询语句本身不存储数据访问时实时查询基表全局永久对象会话临时表会物化保存一份独立数据支持索引仅当前会话可见会话断开自动销毁视图的数据跟随基表变化临时表是静态快照。