AI 助理
备案控制台
开发者社区 数据库 文章 正文

13. Mysql 使用WITH进行复杂和递归查询

2024-06-05 1352
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
云数据库 RDS PostgreSQL，高可用系列 2核4GB
简介： 13. Mysql 使用WITH进行复杂和递归查询

概述

WITH 语句，允许我们使用常用表达式（Common Table Expressions，CTE），CTE是一个临时命名的结果集，它可以在一个查询中被引用多次。使用WITH关键字，我们可以将一个复杂的查询分解成更小的、易于理解的部分，并将这些部分组合在一起以创建最终的查询结果。

使用场景

  • 复杂查询：WITH语句可以用于构建复杂的查询逻辑，将多个子查询组合在一起，提高查询的可读性和维护性。
  • 数据转换：通过WITH语句，可以在查询中创建临时表达式，并对其进行数据转换、筛选、聚合等操作，以满足特定的查询需求。
  • 递归查询：WITH RECURSIVE语法可以用于执行递归查询，即在查询结果中引用自身，常用于处理树状结构或层级关系的数据。

WITH语句的注意事项

  • WITH语句定义的临时表达式只在当前查询中有效，不能在其他查询中引用。
  • WITH语句中的子查询可以引用之前定义的临时表达式，允许多个临时表达式之间的相互引用。
  • WITH语句中的临时表达式可以在后续查询中像普通表一样使用，可以进行联接、过滤、排序等操作。
  • WITH语句中的列名可以省略，此时将使用子查询的列名作为默认列名。
  • WITH语句在MySQL 8.0版本及以上才被支持，旧版本的MySQL不支持此语法。

基本语法

with_clause:
    with [recursive]
        cte_name [(col_name [, col_name] ...)] as (subquery)
        [, cte_name [(col_name [, col_name] ...)] as (subquery)] ...

非递归改进的派生表

# 使用派生表子查询
select max(txt) max_txt, min(txt) min_txt
from (select concat(cte2.txt, cte3.txt) as txt
      from (select concat(cte1.txt, 'is a ') as txt
            from (select 'this ' as txt) as cte1) as cte2,
           (select 'nice query' as txt
            union
            select 'query that rocks'
            union
            select 'query') as cte3) as cte4;
# 使用with非递归查询
with cte1 as (select 'this ' as txt),
     cte2 as (select concat(cte1.txt, 'is a') as txt from cte1),
     cte3 as (select 'nice query' as txt
              union
              select 'query that rocks' as txt
              union
              select 'query' as txt),
     cte4 as (select concat(cte2.txt, cte3.txt) as txt
              from cte2,
                   cte3)
select max(txt) max_txt, min(txt) min_txt
from cte4;
+---------------------------+---------------------+
| max_txt                   | min_txt             |
+---------------------------+---------------------+
| this is aquery that rocks | this is anice query |
+---------------------------+---------------------+


通过以上示例，可以发现使用with语法大大提高查询的可读性，更加简洁。

递归生成序列

递归生成序列的步骤：

  1. 定义根节点，初始值。
  1. 所谓递归迭代，是指每一次递归都要调用上一次查询的结果集，UNION ALL是指每次都把结果集并在一起。
  2. 定义递归终止条件。
# 生成1-6的序列
with recursive my_cte(n) as
                   (select 1  -- 初始值
                    union all
                    select 1 + n
                    from my_cte
                    where n < 6) -- 递归终止条件
select n
from my_cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
# 生成连续日期
with recursive date_list(calendar_date) as (select '2023-12-01' calendar_date
                                            union all
                                            select date_add(calendar_date, interval 1 day) calendar_date
                                            from date_list
                                            where date_add(calendar_date, interval 1 day) <= '2023-12-10')
select calendar_date
from date_list;
+---------------+
| calendar_date |
+---------------+
| 2023-12-01    |
| 2023-12-02    |
| 2023-12-03    |
| 2023-12-04    |
| 2023-12-05    |
| 2023-12-06    |
| 2023-12-07    |
| 2023-12-08    |
| 2023-12-09    |
| 2023-12-10    |
+---------------+
# 斐波那契序列
with recursive my_cte as
                   (select 1 as f, 1 as next_f
                    union all
                    select next_f, f + next_f
                    from my_cte
                    where f < 500)
select f,next_f
from my_cte;
+------+--------+
| f    | next_f |
+------+--------+
|    1 |      1 |
|    1 |      2 |
|    2 |      3 |
|    3 |      5 |
|    5 |      8 |
|    8 |     13 |
|   13 |     21 |
|   21 |     34 |
|   34 |     55 |
|   55 |     89 |
|   89 |    144 |
|  144 |    233 |
|  233 |    377 |
|  377 |    610 |
|  610 |    987 |
+------+--------+


递归生成序列常常应用于生成完整的序列，在实际应用中，实际数据常常伴随着缺失，而使用序列可以更好地验证数据是否缺失情况。

建表、插入、更新和删除中应用

# 建表
create table numbers
with recursive my_cte(n) as
                   (select 1
                    union all
                    select 1 + n
                    from my_cte
                    where n < 6)
select n
from my_cte;
# 插入数据
insert into numbers
with recursive my_cte(n) as
                   (select 1
                    union all
                    select 1 + n
                    from my_cte
                    where n < 6)
select n
from my_cte;
# 更新数据
with recursive my_cte(n) as
                   (select 1
                    union all
                    select 1 + n
                    from my_cte
                    where n < 6)
update numbers, my_cte
set numbers.n=0
where numbers.n = my_cte.n * my_cte.n;
# 删除数据
with recursive my_cte(n) as
                   (select 1
                    union all
                    select 1 + n
                    from my_cte
                    where n < 6)
delete
from numbers
-- delete the numbers greater than the average of 1,...,6 (=3.5)
where numbers.n > (select avg(n) from my_cte);

delete
from numbers
where numbers.n >
      (with recursive my_cte(n) as
                          (select 1
                           union all
                           select 1 + n
                           from my_cte
                           where n < 6)
                      # half the average is 3.5/2=1.75
       select avg(n) / 2
       from my_cte);

with 关键字在许多场景下都可以用，可见非常灵活。

层次结构

常见的层次结构有：

  • 首席执行官->副总裁->经理->员工
  • 项目->子项目->子项目
  • 父母->儿子->孙子
  • 电子邮件或论坛线程（问题->回复->回复回复）
  • 城镇->地区->州

层次结构递归步骤：

  1. 定义根节点，初始值，如parent is null 作为递归查询的起点。
  2. 所谓递归迭代，是指每一次递归都要调用上一次查询的结果集，UNION ALL是指每次都把结果集并在一起。
  3. 迭代公式利用上一次查询返回的结果集执行特定的查询，直到CTE返回NULL或达到最大的迭代次数。数据准备：准备相关电子产品类别层级数据如下。
create table category
(
    category_id   int,
    category_name varchar(255),
    parent        varchar(255)
);
insert into category
select 1 as category_id, 'ELECTRONICS' as name, null as parent
union all
select 2 as category_id, 'TELEVISIONS' as name, 1 as parent
union all
select 3 as category_id, 'TUBE' as name, 2 as parent
union all
select 4 as category_id, 'LCD' as name, 2 as parent
union all
select 5 as category_id, 'PLASMA' as name, 2 as parent
union all
select 6 as category_id, 'PORTABLE ELECTRONICS' as name, 1 as parent
union all
select 7 as category_id, 'MP3 PLAYERS' as name, 6 as parent
union all
select 8 as category_id, 'FLASH' as name, 7 as parent
union all
select 9 as category_id, 'CD PLAYERS' as name, 6 as parent
union all
select 10 as category_id, '2 WAY RADIOS' as name, 6 as parent;

想要查询每个类别对应的父类、类别层级深度（总共有几层）、类别层级路径。

with recursive cte as (select category_id
                            , category_name
                            , parent
                            , category_name                  as parent_name     -- 查询每个类别的父类
                            , 0                              as depth           -- 查询类别层级深度
                            , cast(category_id as char(200)) as path            -- 查询类别层级路径
                       from category
                       where parent is null
                       union all
                       select c.category_id
                            , c.category_name
                            , c.parent
                            , cte.category_name                     as parent_name -- 查询每个类别的父类
                            , cte.depth + 1                         as depth   -- 查询类别层级深度
                            , concat(cte.path, '->', c.category_id) as path    -- 查询类别层级路径
                       from category as c
                                inner join cte
                                           on c.parent = cte.category_id)
select * from cte;
+-------------+----------------------+--------+----------------------+-------+------------+
| category_id | category_name        | parent | parent_name          | depth | path       |
+-------------+----------------------+--------+----------------------+-------+------------+
|           1 | ELECTRONICS          | NULL   | ELECTRONICS          |     0 | 1          |
|           2 | TELEVISIONS          | 1      | ELECTRONICS          |     1 | 1->2       |
|           6 | PORTABLE ELECTRONICS | 1      | ELECTRONICS          |     1 | 1->6       |
|           3 | TUBE                 | 2      | TELEVISIONS          |     2 | 1->2->3    |
|           4 | LCD                  | 2      | TELEVISIONS          |     2 | 1->2->4    |
|           5 | PLASMA               | 2      | TELEVISIONS          |     2 | 1->2->5    |
|           7 | MP3 PLAYERS          | 6      | PORTABLE ELECTRONICS |     2 | 1->6->7    |
|           9 | CD PLAYERS           | 6      | PORTABLE ELECTRONICS |     2 | 1->6->9    |
|          10 | 2 WAY RADIOS         | 6      | PORTABLE ELECTRONICS |     2 | 1->6->10   |
|           8 | FLASH                | 7      | MP3 PLAYERS          |     3 | 1->6->7->8 |
+-------------+----------------------+--------+----------------------+-------+------------+

循环避免

数据准备：

create table rockets
(origin char(20), destination char(20), trip_time int);
insert into rockets values
('earth', 'mars', 2),
('mars', 'jupiter', 3),
('jupiter', 'saturn', 4),
('saturn', 'earth', 9);

数据来看，从地球开始，我们添加火星，然后是木星，然后是土星，然后是地球（因为新的火箭），所以我们又回到了起点—>地球。然后我们永远添加火星…等，进入了循环，执行会报以下错。

[HY000][3636] Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

我们可以通过一下方法来避免循环：

  1. 如果行星已经存在，就不要为结果添加行星。这种重复的消除是通过使用UNION DISTINCT而不是UNION ALL完成的。
with recursive all_destinations as
                   (select destination as planet
                    from rockets
                    where origin = 'earth'
                    union distinct
                    select r.destination
                    from rockets as r,
                         all_destinations as d
                    where r.origin = d.planet)
select *
from all_destinations;
+---------+
| planet  |
+---------+
| mars    |
| jupiter |
| saturn  |
| earth   |
+---------+
  1. 构建一个“路径”列（如深度/宽度），使用find_in_set(r.destination, d.path) = 0进行中断。我们不需要再使用DISTINCT，所以我们使用union all以避免（无用的）重复消除的开销。
with recursive all_destinations as
                   (select destination                    as planet,
                           trip_time                      as total_time,
                           cast(destination as char(500)) as path
                    from rockets
                    where origin = 'earth'
                    union all
                    select r.destination,
                           d.total_time + r.trip_time,
                           concat(d.path, ',', r.destination)
                    from rockets r,
                         all_destinations d
                    where r.origin = d.planet
                      and find_in_set(r.destination, d.path) = 0)
select * from all_destinations;
+---------+------------+---------------------------+
| planet  | total_time | path                      |
+---------+------------+---------------------------+
| mars    |          2 | mars                      |
| jupiter |          5 | mars,jupiter              |
| saturn  |          9 | mars,jupiter,saturn       |
| earth   |         18 | mars,jupiter,saturn,earth |
+---------+------------+---------------------------+
# 或者也可以通过以下方法过滤掉
with recursive all_destinations as
                   (select destination                    as planet,
                           trip_time                      as total_time,
                           cast(destination as char(500)) as path,
                           0                              as is_cycle
                    from rockets
                    where origin = 'earth'
                    union all
                    select r.destination,
                           d.total_time + r.trip_time,
                           concat(d.path, ',', r.destination),
                           find_in_set(r.destination, d.path) != 0
                    from rockets r,
                         all_destinations d
                    where r.origin = d.planet
                      and is_cycle = 0)
select * from all_destinations where is_cycle = 0;
+---------+------------+---------------------------+----------+
| planet  | total_time | path                      | is_cycle |
+---------+------------+---------------------------+----------+
| mars    |          2 | mars                      |        0 |
| jupiter |          5 | mars,jupiter              |        0 |
| saturn  |          9 | mars,jupiter,saturn       |        0 |
| earth   |         18 | mars,jupiter,saturn,earth |        0 |
+---------+------------+---------------------------+----------+

总结

WITH语句是MySQL中一种强大的查询语法，可以创建临时表达式并在后续查询中引用。它广泛应用于递归查询、复杂查询和数据转换等场景，提高了查询的灵活性和可读性。使用WITH语句时，需要注意其语法规则和限制，以确保正确使用和理解其功能。关于递归查询也经常在面试中考察，可以多动手实验一下，并深度理解它。

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版查询
云数据库 RDS MySQL 版递归
云数据库 RDS MySQL 版递归查询
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
3hvg2zg3cgsi6
目录
相关文章
艾伦~耶格尔
|
27天前
|
SQL 缓存 监控
MySQL缓存机制：查询缓存与缓冲池优化
MySQL缓存机制是提升数据库性能的关键。本文深入解析了MySQL的缓存体系，包括已弃用的查询缓存和核心的InnoDB缓冲池，帮助理解缓存优化原理。通过合理配置，可显著提升数据库性能，甚至达到10倍以上的效果。
艾伦~耶格尔
68 1
艾伦~耶格尔
|
29天前
|
SQL 存储 关系型数据库
MySQL体系结构详解：一条SQL查询的旅程
本文深入解析MySQL内部架构，从SQL查询的执行流程到性能优化技巧，涵盖连接建立、查询处理、执行阶段及存储引擎工作机制，帮助开发者理解MySQL运行原理并提升数据库性能。
艾伦~耶格尔
103 0
互联网课堂
|
2月前
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询？
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据，传统的浅分页和深分页查询效率较低，尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法，有效提升了查询速度。最终建议根据业务需求选择合适方案：浅分页可加单列索引，深分页推荐联合索引或子查询优化，同时结合前端传递最后一条数据ID的方式实现高效翻页。
互联网课堂
141 0
蓝易云
|
9天前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
储存过程(Stored Procedures) 和 函数(Functions) : 储存过程和函数允许用户编写 SQL 脚本执行复杂任务.
蓝易云
112 14
蓝易云
|
11天前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
蓝易云
58 15
艾伦~耶格尔
|
28天前
|
SQL 监控 关系型数据库
SQL优化技巧：让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧，涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面，帮助开发者显著提升数据库性能，解决慢查询问题，适用于高并发与大数据场景。
艾伦~耶格尔
98 2
艾伦~耶格尔
|
29天前
|
SQL 关系型数据库 MySQL
MySQL入门指南：从安装到第一个查询
本文为MySQL数据库入门指南，内容涵盖从安装配置到基础操作与SQL语法的详细教程。文章首先介绍在Windows、macOS和Linux系统中安装MySQL的步骤，并指导进行初始配置和安全设置。随后讲解数据库和表的创建与管理，包括表结构设计、字段定义和约束设置。接着系统介绍SQL语句的基本操作，如插入、查询、更新和删除数据。此外，文章还涉及高级查询技巧，包括多表连接、聚合函数和子查询的应用。通过实战案例，帮助读者掌握复杂查询与数据修改。最后附有常见问题解答和实用技巧，如数据导入导出和常用函数使用。适合初学者快速入门MySQL数据库，助力数据库技能提升。
艾伦~耶格尔
111 4
蓝易云
|
1月前
|
存储 关系型数据库 MySQL
使用命令行cmd查询MySQL表结构信息技巧分享。
掌握了这些命令和技巧，您就能快速并有效地从命令行中查询MySQL表的结构信息，进而支持数据库维护、架构审查和优化等工作。
蓝易云
203 9
艾伦~耶格尔
|
29天前
|
SQL 监控 关系型数据库
MySQL高级查询技巧：子查询、联接与集合操作
本文深入解析了MySQL高级查询的核心技术，包括子查询、联接和集合操作，通过实际业务场景展示了其语法、性能差异和适用场景，并提供大量可复用的代码示例，助你从SQL新手进阶为数据操作高手。
艾伦~耶格尔
42 0
德国都芳最好
|
10月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中，需要根据具体的业务需求和查询模式，综合运用索引策略和查询性能调优方法，不断地测试和优化，以提高MySQL数据库的查询性能。
德国都芳最好
570 66

热门文章

最新文章

  • 1
    MySQL数据库连接过多（Too many connections）错误处理策略
  • 2
    CentOS 7 yum 安装 MySQL教程
  • 3
    查寻MySQL或SQL Server的连接数，并配置超时时间和最大连接量
  • 4
    安装与配置MySQL 8 on Ubuntu，包括权限授予、数据库备份及远程连接指南
  • 5
    Mysql基础学习day01
  • 6
    MySQL服务器配置优化：my.cnf参数调优指南
  • 7
    MySQL入门指南：从安装到第一个查询
  • 8
    MySQL锁机制：并发控制与事务隔离
  • 9
    阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
  • 10
    MySQL体系结构详解：一条SQL查询的旅程
  • 1
    Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
    81
  • 2
    阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
    136
  • 3
    阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
    112
  • 4
    CentOS 7 yum 安装 MySQL教程
    203
  • 5
    MySQL的查询操作语法要点
    112
  • 6
    MySQL的查询操作语法要点
    58
  • 7
    如何创建RDS MySQL定时任务？
    58
  • 8
    PHP和Mysql前后端交互效果实现
    123
  • 9
    如何将Excel表的数据导入RDS MySQL数据库？
    87
  • 10
    MySql事务以及事务的四大特性
    40

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    2025云栖大会，阿里云百炼邀请您的参与和见证