【MySQL】MySQL 8.0 新特性之 - 公用表表达式（CTE）

已于 2023-02-11 09:08:08 修改
阅读量2.3k 收藏 23
点赞数 18
分类专栏： MySQL 文章标签： mysql 数据库 java
于 2023-02-09 21:57:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42201180/article/details/127354581
版权

MySQL 8.0 新特性之 - 公用表表达式（CTE）

1. 公用表表达式（CTE） - WITH 介绍

1.1 公用表表表达式

1.1.1 什么是公用表表达式

官网：https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions

MySQL 从 8.0 开始支持 WITH 语法，即：Common Table Expressions - CTE，公用表表达式。

CTE 是一个命名的临时结果集合，仅在单个 SQL 语句（select、insert、update 或 delete）的执行范围内存在。

与派生表类似的是：CTE 不作为对象存储，仅在查询执行期间持续。与派生表不同的是：CTE 可以是自引用（递归CTE），也可以在同一查询中多次引用。此外，与派生表相比，CTE 提供了更好的可读性和性能。

1.1.2 CTE 语法

CTE 的结构包括：名称、可选列列表和定义 CTE 的查询。定义 CTE 后，可以像 select、insert、update、delete 或 create view 语句中的视图一样使用它。

with cte_name (column_list) as (
    query
    )
select * from cte_name;

查询中的列数必须与 column_list 中的列数相同。 如果省略 column_list，CTE 将使用定义 CTE 的查询的列列表。

1.1.3 CTE示例

初始化数据：

-- create table
create table department
(
    id        bigint auto_increment comment '主键ID'
        primary key,
    dept_name varchar(32)      not null comment '部门名称',
    parent_id bigint default 0 not null comment '父级id'
);

-- insert values
insert into `department` values (null, '总部', 0);
insert into `department` values (null, '研发部', 1);
insert into `department` values (null, '测试部', 1);
insert into `department` values (null, '产品部', 1);
insert into `department` values (null, 'Java组', 2);
insert into `department` values (null, 'Python组', 2);
insert into `department` values (null, '前端组', 2);
insert into `department` values (null, '供应链测试组', 3);
insert into `department` values (null, '商城测试组', 3);
insert into `department` values (null, '供应链产品组', 4);
insert into `department` values (null, '商城产品组', 4);
insert into `department` values (null, 'Java1组', 5);
insert into `department` values (null, 'Java2组', 5);

（1）最基本的CTE语法：

mysql> with cte1 as (select * from `department` where id in (1, 2)),
    ->      cte2 as (select * from `department` where id in (2, 3))
    -> select *
    -> from cte1
    ->          join cte2
    -> where cte1.id = cte2.id;
+----+-----------+-----------+----+-----------+-----------+
| id | dept_name | parent_id | id | dept_name | parent_id |
+----+-----------+-----------+----+-----------+-----------+
|  2 | 研发部     |         1 |  2 | 研发部     |         1 |
+----+-----------+-----------+----+-----------+-----------+
1 row in set (0.00 sec)

（2）一个 CTE 引用另一个 CTE

mysql> with cte1 as (select * from `department` where id = 1),
    ->      cte2 as (select * from cte1)
    -> select *
    -> from cte2;
+----+-----------+-----------+
| id | dept_name | parent_id |
+----+-----------+-----------+
|  1 | 总部       |         0 |
+----+-----------+-----------+
1 row in set (0.00 sec)

1.3 递归 CTE

1.3.1 递归 CTE 简介

递归 CTE 是一个具有引用 CTE 名称本身的子查询的 CTE。递归 CTE 的语法为：

with recursive cte_name as (
    initial_query  -- anchor member
union all
recursive_query -- recursive member that references to the cte name
)
select * from cte_name;

递归 CTE 由三个主要部分组成：

  • 形成 CTE 结构的基本结果集的初始查询（initial_query），初始查询部分被称为锚成员。

  • 递归查询部分是引用 CTE 名称的查询，因此称为递归成员。递归成员由一个 union all 或 union distinct 运算符与锚成员相连。

  • 终止条件是当递归成员没有返回任何行时，确保递归停止。

递归 CTE 的执行顺序如下：

  1. 首先，将成员分为两个：锚点和递归成员。
  2. 接下来，执行锚成员形成基本结果集（R0），并使用该基本结果集进行下一次迭代。
  3. 然后，将 Ri 结果集作为输入执行递归成员，并将 Ri + 1 作为输出。
  4. 之后，重复第三步，直到递归成员返回一个空结果集，换句话说，满足终止条件。
  5. 最后，使用 union all 运算符将结果集从 R0 到 Rn 组合。

1.3.2 递归成员限制

递归成功不能包含以下结构：

  • 聚合函数，如 max、min、sum、avg、count 等。
  • group by 子句
  • order by 子句
  • limit 子句
  • distinct

上述约束不适用于锚点成员。 另外，只有在使用 union 运算符时，要禁止 distinct 才适用。 如果使用 union distinct 运算符，则允许使用 distinct。

另外，递归成员只能在其子句中引用 CTE 名称，而不是引用任何子查询。

1.3.3 递归 CTE 示例

with recursive cte_count (n)
                   as (
        select 1
        union all
        select n + 1
        from cte_count
        where n < 3
    )
select n from cte_count;

在此示例中，以下查询：

select 1

是作为基本结果集返回 1 的锚成员。

以下查询：

select n + 1
from cte_count
where n < 3

是递归成员，因为它引用了 cte_count 的 CTE 名称。递归成员中的表达式 < 3 是终止条件。当 n 等于 3，递归成员将返回一个空集合，将停止递归。

下图显示了上述 CTE 的元素：

在这里插入图片描述
递归 CTE 返回以下输出：

+------+
|    n |
+------+
|    1 |
|    2 |
|    3 |
+------+

递归 CTE 的执行步骤如下：

  1. 首先，分离锚和递归成员。
  2. 接下来，锚定成员形成初始行 select 1，因此第一次迭代在 n = 1 时产生 1 + 1 = 2。
  3. 然后，第二次迭代对第一次迭代的输出 2 进行操作，并且在 n = 2 时产生 2 + 1 = 3。
  4. 之后，在第三次操作 n = 3 之前，满足终止条件 n <3 ，因此查询停止。
  5. 最后，使用 union all 运算符组合所有结果集 1，2 和 3。

1.3.4 使用递归 CTE 遍历分层数据

查部门 id = 2 的所有下级部门和本级：

mysql> with recursive cte_tab as (select id, dept_name, parent_id, 1 as level
    ->                            from department
    ->                            where id = 2
    ->                            union all
    ->                            select d.id, d.dept_name, d.parent_id, level + 1
    ->                            from cte_tab c
    ->                                     inner join department d on c.id = d.parent_id
    -> )
    -> select *
    -> from cte_tab;
+------+-----------+-----------+-------+
| id   | dept_name | parent_id | level |
+------+-----------+-----------+-------+
|    2 | 研发部     |         1 |     1 |
|    5 | Java组    |         2 |     2 |
|    6 | Python组  |         2 |     2 |
|    7 | 前端组     |         2 |     2 |
|   12 | Java1组   |         5 |     3 |
|   13 | Java2组   |         5 |     3 |
+------+-----------+-----------+-------+
6 rows in set (0.00 sec)

2. CTE 与 Derived Table

针对 from 子句里面的 subquery，MySQL 在不同版本中，是做过一系列的优化，接下来我们就来看看。

在 5.6 版本中

MySQL 会对每一个 Derived Table 进行物化，生成一个临时表保存 Derived Table 的结果，然后利用临时表来完成父查询的操作，具体如下：

mysql> explain
    -> select * from (select * from department where id <= 1000) t1 join (select * from department where id >= 990) t2 on t1.id = t2.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                        | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | range  | PRIMARY       | PRIMARY | 8       | NULL                       |    1 |   100.00 | Using where |
|  1 | SIMPLE      | department | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | pointer_mall.department.id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

在 5.7 版本中

MySQL 引入了 Derived Merge 新特性，允许符合条件的 Derived Table 中的子表与父查询的表进行合并，具体如下：

mysql> explain
    -> select * from (select * from department where id <= 1000) t1 join (select * from department where id >= 990) t2 on t1.id = t2.id;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL        | NULL    | NULL  |    1900 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived3> | NULL       | ref   | <auto_key0>   | <auto_key0> | 8       | t1.id |    2563 |   100.00 | NULL        |
|  3 | DERIVED     | department | NULL       | range | PRIMARY       | PRIMARY     | 8       | NULL  | 4870486 |   100.00 | Using where |
|  2 | DERIVED     | department | NULL       | range | PRIMARY       | PRIMARY     | 8       | NULL  |    1900 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)

在 8.0 版本中

我们可以使用 CTE 实现，其执行计划也是和 Derived Table 一样

mysql> explain
    -> with t1 as (select * from department where id <= 1000),
    ->      t2 as (select * from department where id >= 990)
    -> select * from t1 join t2 on t1.id = t2.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                        | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | range  | PRIMARY       | PRIMARY | 8       | NULL                       |    1 |   100.00 | Using where |
|  1 | SIMPLE      | department | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | pointer_mall.department.id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+----------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

从测试结果来看，CTE 似乎是 Derived Table 的一个替代品？其实不是的，虽然 CTE 内部优化流程与 Derived Table 类似，但是两者还是区别的，具体如下：

  1. 一个 CTE 可以引用另一个 CTE

  2. CTE 可以自引用

  3. CTE 在语句级别生成临时表，多次调用只需要执行一次，提高性能

从上面介绍可以知道，CTE 一方面可以非常方便进行 SQL 开发，另一方面也可以提升 SQL 执行效率。

奥特迦
关注
  • 18
    点赞
  • 踩
  • 23
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 18
    评论
专栏目录
mysql cte递归_CTE 递归查询全解
weixin_39743423的博客
02-07 1775
TSQL脚本能实现递归查询，用户使用共用表表达式 CTE(Common Table Expression)，只需要编写少量的代码，就能实现递归查询。本文详细介绍CTE递归调用的特性和使用示例，递归查询主要用于层次结构的查询，从叶级(Leaf Level)向顶层(Root Level)查询，或从顶层向叶级查询，或递归的路径(Path)。一，递归查询原理递归调用是指自己调用自己，使用CTE实现递归查询...
公用表表达式（CTE）详解：针对 MySQL 和 SQL Server 数据库
最新发布
nbsaas-boot基于Request-Response的企业级快速开发框架
05-21 2110
CTE 是一个命名的临时结果集，它在查询的执行范围内有效。CTE 的定义在WITH子句中，紧随其后的是查询主体。非递归 CTE：用于定义一次性计算结果的临时表。递归 CTE：用于处理递归查询，如处理层次结构数据（例如组织结构图）。
MySQL CTE 通用表表达式：基础学习
常备不懈
03-06 839
CTE（Common Table Expressions），是一个可以在单个语句范围内被创建的临时结果集，可在该语句中被多次引用。
sql server 公用表表达式【CTE 】、临时表、变量表
u013400314的博客
10-27 2104
5.如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。1.使用with子句可以让子查询重用相同的with查询块,通过select调用（with子句只能被select查询块引用），一般在with查询用到多次情况下。
SQL 知识你知道吗：公共表表达式（CTE）
暗星涌动
06-12 864
公共表表达式（CTE）是一种临时命名查询结果集的方式，可以在一个查询语句中多次引用。CTE在查询语句中定义，可以在查询语句中的任何位置使用，并且只在查询语句执行期间存在。CTE通常用于简化复杂的查询，提高查询的可读性和可维护性。CTE的语法如下：其中，cte_name是CTE的名称，可以在查询语句的其他部分中使用。column1, column2等是CTE返回的列的名称。SELECT语句定义了CTE的查询结果集。最后的SELECT语句可以引用CTE并过滤、排序、分组等操作。
CTE的介绍与使用
haibucuoba的博客
06-11 5182
1、什么是CTE CTE可以被认为是在单个SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。CTE类似于派生表，因为它不作为对象存储，并且仅在查询期间持续。与派生表不同，CTE可以自引用，并且可以在同一查询中多次使用。 2、CTE作用： 创建递归查询 在一般不是必要使用视图时替换视图，这样的话，...
mysql 中用到的一些函数、表达式汇总
qq_37537009的博客
06-15 784
平时遇到过的一些sql函数表达式，例如 IFNULL() 、case when then else end等
mysql8 公用表表达式CTE的使用方法实例分析
09-08
MySQL 8中的公用表表达式（Common Table Expression，简称CTE）是一种强大的查询工具，它允许你在SQL语句中创建临时的结果集，这个结果集可以在同一语句的其他部分中重复使用。CTE的引入极大地方便了复杂查询的编写...
mysql-connector-java-8.0.31-jar包
01-13
这包括对InnoDB存储引擎、JSON数据类型、窗口函数和Common Table Expressions (CTE)等新特性的支持。 6. **错误处理和诊断**：当数据库操作遇到问题时，Connector/J会提供详细的错误信息和异常处理，帮助开发者快速...
SQL Server 公用表表达式(CTE)实现递归的方法
09-09
SQL Server中的公用表表达式（CTE，Common Table Expression）是一种强大的工具，它允许你在复杂的查询中定义一个临时的结果集，这个结果集可以被查询本身多次引用。CTE的使用非常灵活，尤其在处理递归关系时表现得...
mysql-8.0.34-winx64.zip
10-07
MySQL是世界上最受欢迎的开源关系型数据库管理系统之一，其最新版本为8.0.34，专为Windows系统设计的版本是“mysql-8.0.34-winx64.zip”。这个压缩包包含了在Windows环境下运行MySQL所需的所有核心组件，包括服务器...
MySQL · 新特性分析 · CTE执行过程与实现原理
weixin_34417200的博客
03-08 369
众所周知，Common table expression(CTE)是在大多数的关系型数据库里都存在的特性，包括ORACLE, SQLSERVER,POSTGRESQL等，唯独开源数据库老大MySQL缺失。CTE作为一个方便用户使用的功能，原本是可以利用普通的SQL语句替代的，但是对于复杂的CTE来说，要模拟出CTE的效果还是需要很大的功夫。如果考虑性能那...
mysql8迭代查询_MySQL8.0.19-通过Limit调试递归CTE
weixin_39888943的博客
01-28 536
作者：Guilhem Bichot 译：徐轶韬在MySQL 8.0.1中，我们引入了对递归通用表表达式(CTE)的支持。今天，我想提出一个解决方案，当使用递归CTE编写查询时，几乎每个人都会遇到：发生无限递归时，如何调试？考虑以下示例查询，该查询生成从1到5的整数： 此查询正常执行，这是它的结果： 现在，假设我在键入它时犯了一个小错误(这只是键盘意外，在“ MySQL现在将错误的WHERE条件转...
高性能MySQL实战课
05-21
限时福利1：购课进答疑群专享柳峰（刘运强）老师答疑服务 为什么需要掌握高性能的MySQL实战？ 由于互联网产品用户量大、高并发请求场景多，因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能，也是衡量一个程序员能力和薪资的标准之一。 为了让大家快速系统了解高性能MySQL核心知识全貌，我为你总结了「高性能 MySQL 知识框架图」,帮你梳理学习重点，建议收藏！ 【课程设计】 课程分为四大篇章，将为你建立完整的 MySQL 知识体系，同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。 一、性能优化篇： 主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL 的索引结构 B+Tree ，学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引，从而彻底掌握索引的使用和优化技巧，能够避开很多实战中遇到的“坑”。 二、MySQL 8.0新特性篇： 主要包括窗口函数和通用表表达式。企业中的许多报表统计需求，如果不采用窗口函数，用普通的 SQL 语句是很难实现的。 三、高性能架构篇： 主要包括主从复制和读写分离。在企业的生产环境中，很少采用单台MySQL节点的情况，因为一旦单个节点发生故障，整个系统都不可用，后果往往不堪设想，因此掌握高可用架构的实现是非常有必要的。 四、面试篇： 程序员获得工作的第一步，就是高效的准备面试，面试篇主要从知识点回顾总结的角度出发，结合程序员面试高频MySQL问题精讲精练，帮助程序员吊打面试官，获得心仪的工作机会。
CTE的使用方法和注意事项
热门推荐
朱燚
06-09 1万+
我先简单介绍一下CTE(Common Table Expression)是什么 ,然后简要介绍下用法和注意事项什么是CTE:Common Table Expression:是Sql2005推出的语法,类似内置临时表,创建后自动消亡,在cte中可以进行递归查询等操作cte可以看作临时表,但是它的生命周期仅存在于访问每一次的TSQL批处理语法中,而一般临时对象的生命周期与连接同在一、生命周
mysql cte的好处_Mysql 8 重要新特性 - CTE 通用表表达式
weixin_33901995的博客
02-07 429
前言Mysql 8 正式发布了，新增了很多优秀特性，之后我会挑些重点来分享。下面和大家一起熟悉下CTE(Common Table Expressions)通用表表达式。CTE 是什么派生表大家都比较熟悉了，CTE 就是针对派生表来的，可以说是增强的派生表，或者说时派生表的替换。派生表是FROM中的子查询，例如：SELECT ... FROM (subquery) AS derived, t1 .....
11-MySQL-8 公用表表达式CTE的使用分析
qq_40329851的博客
02-08 164
公用表表达式CTE就是命名的临时结果集，作用范围是当前语句。 说白了，你可以把它理解成一个可以多次重复使用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。 自MySQL 8.0版以来简要介绍了公共表表达式或叫CTE的功能，因此需要您在计算机上安装MySQL 8.0，因此以下语句仅适合在MySQL8.0 以上版本。 一、CTE 表达式 1、CTE的结构包括名称，可选列列表和定义CTE的查询。 定义CTE后，可以像SELECT，INSERT，UPDATE，DELETE
MySQL-公用表达式(CTE)
倪先生的博客
03-31 904
简介：公用表表达式是一个命名的临时结果集，仅在单个SQL语句(例如SELECT，INSERT，UPDATE或DELETE)的执行范围内存在。与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。 与派生表不同，CTE可以是自引用(递归CTE)，也可以在同一查询中多次引用。 此外，与派生表相比，CTE提供了更好的可读性和性能。 语法： WITH cte_name (column_list) AS...
mysql8.0 新特性
03-28
1. Window functions: MySQL 8.0 now supports window functions, which allows users to perform complex analytical queries with ease. 2. Common Table Expressions (CTEs): CTEs are temporary result sets that can be used within a single SQL statement. This feature makes it easier to write complex queries and improves code readability. 3. Recursive Common Table Expressions (RCTEs): RCTEs are a special type of CTE that allows users to write recursive queries. This feature is useful for queries that involve hierarchical data. 4. JSON enhancements: MySQL 8.0 includes several enhancements to its JSON support, including better performance and improved functionality. 5. Invisible indexes: This feature allows users to mark an index as "invisible," which means that it will not be used by the query optimizer. This can be useful for testing the impact of removing an index without actually removing it. 6. Instant ADD COLUMN: MySQL 8.0 includes a new feature that allows users to add a new column to a table without copying the entire table. This can be useful for large tables with many columns. 7. Roles: MySQL 8.0 introduces the concept of roles, which allows users to define sets of privileges that can be assigned to multiple users. 8. Improved performance: MySQL 8.0 includes several performance improvements, including better support for multi-threaded queries and faster data loading. 9. Improved security: MySQL 8.0 includes several security enhancements, including improved password management and support for encrypted connections. 10. Improved replication: MySQL 8.0 includes several improvements to its replication capabilities, including support for multiple replication channels and better conflict detection and resolution.

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 18
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

奥特迦

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值