教你如何使用MySQL8递归的方法

之前写过一篇 MySQL通过自定义函数的方式，递归查询树结构，从MySQL 8.0 开始终于支持了递归查询的语法

CTE

首先了解一下什么是 CTE，全名 Common Table Expressions

WITH
 cte1 AS (SELECT a, b FROM table1),
 cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

cte1, cte2 为我们定义的CTE，可以在当前查询中引用

可以看出 CTE 就是一个临时结果集，和派生表类似，二者的区别这里不细说，可以参考下MySQL开发文档：https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples

递归查询

先来看下递归查询的语法

WITH RECURSIVE cte_name AS
(
  SELECT ...   -- return initial row set
  UNION ALL / UNION DISTINCT
  SELECT ...   -- return additional row sets
)
SELECT * FROM cte;
  • 定义一个CTE，这个CTE 最终的结果集就是我们想要的 ”递归得到的树结构"，RECURSIVE 代表当前 CTE 是递归的
  • 第一个SELECT 为 “初始结果集”
  • 第二个SELECT 为递归部分，利用 "初始结果集/上一次递归返回的结果集" 进行查询得到 “新的结果集”
  • 直到递归部分结果集返回为null，查询结束
  • 最终UNION ALL 会将上述步骤中的所有结果集合并（UNION DISTINCT 会进行去重），再通过 SELECT * FROM cte; 拿到所有的结果集

递归部分不能包括：

  • 聚合函数例如 SUM()
  • GROUP BY
  • ORDER BY
  • LIMIT
  • DISTINCT

上面的讲解可能有点抽象，通过例子慢慢来理解

WITH RECURSIVE cte (n) AS -- 这里定义的n相当于结果集的列名，也可在下面查询中定义
(
 SELECT 1
 UNION ALL
 SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

-- result
+------+
| n  |
+------+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+------+
  • 初始结果集为 n =1
  • 这时候看递归部分，第一次执行 CTE结果集即是 n =1，条件发现并不满足 n < 5，返回 n + 1
  • 第二次执行递归部分，CTE结果集为 n = 2，递归... 直至条件不满足
  • 最后合并结果集

EXAMPLE

最后来看一个树结构的例子

CREATE TABLE `c_tree` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `cname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `parent_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mysql> select * from c_tree;
+----+---------+-----------+
| id | cname  | parent_id |
+----+---------+-----------+
| 1 | 1    |     0 |
| 2 | 2    |     0 |
| 3 | 3    |     0 |
| 4 | 1-1   |     1 |
| 5 | 1-2   |     1 |
| 6 | 2-1   |     2 |
| 7 | 2-2   |     2 |
| 8 | 3-1   |     3 |
| 9 | 3-1-1  |     8 |
| 10 | 3-1-2  |     8 |
| 11 | 3-1-1-1 |     9 |
| 12 | 3-2   |     3 |
+----+---------+-----------+
mysql>
WITH RECURSIVE tree_cte as
(
  select * from c_tree where parent_id = 3
  UNION ALL
  select t.* from c_tree t inner join tree_cte tcte on t.parent_id = tcte.id
)
SELECT * FROM tree_cte;
+----+---------+-----------+
| id | cname  | parent_id |
+----+---------+-----------+
| 8 | 3-1   |     3 |
| 12 | 3-2   |     3 |
| 9 | 3-1-1  |     8 |
| 10 | 3-1-2  |     8 |
| 11 | 3-1-1-1 |     9 |
+----+---------+-----------+
  • 初始结果集R0 = select * from c_tree where parent_id = 3
  • 递归部分，第一次 R0 与 c_tree inner join 得到 R1
  • R1 再与 c_tree inner join 得到 R2
  • ...
  • 合并所有结果集 R0 + ... + Ri

更多信息

https://dev.mysql.com/doc/refman/8.0/en/with.html

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • MySQL递归查询树状表的子节点、父节点具体实现

    简介:mysql5.0.94版本,该版本以及较高级的版本(5.5.6等等)尚未支持循环递归查询,和sqlserver.oracle相比,mysql难于在树状表中层层遍历的子节点.本程序重点参考了下面的资料,写了两个sql存储过程,子节点查询算是照搬了,父节点查询是逆思维弄的. 表结构和表数据就不公示了,查询的表user_role,主键是id,每条记录有parentid字段(对应该记录的父节点,当然,一个父节点自然会有一个以上的子节点嘛) 复制代码 代码如下: CREATE FUNCTION `g

  • Mysql树形递归查询的实现方法

    前言 对于数据库中的树形结构数据,如部门表,有时候,我们需要知道某部门的所有下属部分或者某部分的所有上级部门,这时候就需要用到mysql的递归查询 最近在做项目迁移,Oracle版本的迁到Mysql版本,遇到有些oracle的函数,mysql并没有,所以就只好想自定义函数或者找到替换函数的方法进行改造. Oracle递归查询 oracle实现递归查询的话,就可以使用start with ... connect by connect by递归查询基本语法是: select 1 from 表格 st

  • php+mysql不用递归实现的无限级分类实例(非递归)

    要实现无限级分类,递归一般是第一个也是最容易想到的,但是递归一般被认为占用资源的方法,所以很多系统是不考虑使用递归的 本文还是通过数据库的设计,用一句sql语句实现 数据库字段大概如下: 复制代码 代码如下: id 编号 fid 父分类编号 class_name 分类名 path 分类路径,以 id 为节点,组成类似 ,1,2,3,4, 这样的字符串 可以假设有如下的数据: 复制代码 代码如下: id fid class_name path 1  0       分类1 ,       1, 2

  • MySQL通过自定义函数实现递归查询父级ID或者子级ID

    背 景: 在MySQL中如果是有限的层次,比如我们事先如果可以确定这个树的最大深度, 那么所有节点为根的树的深度均不会超过树的最大深度,则我们可以直接通过left join来实现. 但很多时候我们是无法控制或者是知道树的深度的.这时就需要在MySQL中用存储过程(函数)来实现或者在程序中使用递归来实现.本文讨论在MySQL中使用函数来实现的方法: 一.环境准备 1.建表 CREATE TABLE `table_name` ( `id` int(11) NOT NULL AUTO_INCREMEN

  • PHP递归写入MySQL实现无限级分类数据操作示例

    本文实例讲述了PHP递归写入MySQL实现无限级分类数据操作.分享给大家供大家参考,具体如下: PHP递归写入MySQL无限级分类数据,表结构: CREATE TABLE `kepler_goods_category` ( `id` int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, `parentid` int unsigned NOT NULL default 0 comment '父级分类ID', `name` varchar(255) NO

  • MySQL之递归小问题

    mysql本身不支持递归语法,但可通过自连接变相实现一些简单的递归 --递归小方法:临时表和普通表的不同方法 --这题使用的是2次临时表查询父节点的递归 drop table if exists test; create table test( id varchar(100), name varchar(20), parentid varchar(100) ); insert test select '13ed38f1-3c24-dd81-492f-673686dff0f3', '大学教师',

  • 使用递归删除树形结构的所有子节点(java和mysql实现)

    1.业务场景 有如下树形结构: +-0 +-1 +-2 +-4 +-5 +-3 如果删除某个父节点,则其子节点,以及其子节点的子节点,以此类推,需要全部删除. 2.Java实现 使用Map存储树形结构的数据,id为map的key,pid为树形结构的value. import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.uti

  • 教你如何使用MySQL8递归的方法

    之前写过一篇 MySQL通过自定义函数的方式,递归查询树结构,从MySQL 8.0 开始终于支持了递归查询的语法 CTE 首先了解一下什么是 CTE,全名 Common Table Expressions WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c; cte1, cte2 为我们定义的C

  • Python多层嵌套list的递归处理方法(推荐)

    问题:用Python处理一个多层嵌套list ['and', 'B', ['not', 'A'],[1,2,1,[2,1],[1,1,[2,2,1]]], ['not', 'A', 'A'],['or', 'A', 'B' ,'A'] , 'B'] 需求1)如何展开成一层? 需求2)如何删除重复的元素? 包括重复的list, 要考虑子list的重复元素删除后造成的子list重复 #!/usr/bin/env python # -*- coding: utf-8 -*- def unilist(l

  • python二分查找算法的递归实现方法

    本文实例讲述了python二分查找算法的递归实现方法.分享给大家供大家参考,具体如下: 这里先提供一段二分查找的代码: def binarySearch(alist, item): first = 0 last = len(alist)-1 found = False while first<=last and not found: midpoint = (first + last)//2 if alist[midpoint] == item: found = True else: if ite

  • SQL Server 公用表表达式(CTE)实现递归的方法

    SQL Server 公用表表达式(CTE)实现递归的方法

    公用表表达式简介: 公用表表达式 (CTE) 可以认为是在单个 SELECT.INSERT.UPDATE.DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集.CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效.与派生表的不同之处在于,公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE.递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式. 下面先创建一个表,并插入一些数据: crea

  • 关于python之字典的嵌套,递归调用方法

    一 字典的嵌套 在机器学习实战决策树部分,生成决策树时用到了字典的嵌套. >>>s1={'no surface':{}} >>>s1['no surfacce'][0]='no' >>>s1 {'no surface':{0:'no'}} >>>s2={'flipper':{}} >>>s2['flipper'][0]='no' >>>s2['flipper'][1]='yes' >>&

  • 在IDEA的maven项目中连接并使用MySQL8.0的方法教程

    首先看一下我的基本的开发环境: 操作系统:MacOS 10.13.5编辑器:IDEA 2018.3其他:MySQL8.0.15.Maven 3.3.9.JDK 1.8 好,下面就正式开始: 第一步:在IDEA中新建一个maven项目 1.使用骨架创建maven项目,此处选择:maven-archetype-quickstart 2.填入GroupId和ArtifactId 3.第一个选中maven安装的文件夹,第二个选中maven安装文件夹中的conf/settings.xml,第三个如果set

  • Docker 部署 Mysql8.0的方法示例

    1. 参照官网,安装docker 2.拉取mysql镜像 (默认拉取最新的镜像)8.0.11 docker pull mysql 3.在宿主机创建持久化 mysql data 及mysql.cnf mkdir /usr/local/mysqlData/test/cnf mkdir /usr/local/mysqlData/test/data vi /usr/loal/mysqlData/test/cnf/mysql.cnf 设置本地文件共享: Docker -> Preferences... -

  • 教你用Python3+mysql8.0搭建Django框架

    教你用Python3+mysql8.0搭建Django框架

    一.安装python 基础环境:centos7.6 安装: 下载python: https://www.python.org/ftp/python/3.8.2/Python-3.8.2.tgz 解决依赖性: yum install gcc openssl-devel bzip2-devel libffi-devel install gcc-c++ 解压: tar zxf Python3.8.2.tgz 切换到解压后的文件并编译: cd Python-3.8.2 ./configure --pre

  • 教你Clion调试ROS包的方法

    教你Clion调试ROS包的方法

    产品概述 Clion是一款专门开发C以及C++所设计的跨平台的IDE.它是以IntelliJ为基础设计的,包含了许多智能功能来提高开发人员的生产力.这种强大的IDE帮助开发人员在Linux.OS X和Windows上来开发C/C++,同时它还能使用智能编辑器来提高代码质量.自动代码重构并且深度整合Cmake编译系统,从而提高开发人员的工作效率. 重要功能简介 提供C以及C++支持(包含C++,libc++和Boost),同时也支持JavaScript,XML,HTML和CSS.跨平台:您可以在6

  • 教你编译pjsip源码的方法

    教你编译pjsip源码的方法

    操作系统 : Windows 10_x64 [版本 10.0.19042.685] pjsip版本 : 2.10 pjsip官网:https://www.pjsip.org/ 1. 下载pjsip源代码 直接从pjsip官网给的github地址下载 https://github.com/pjsip/pjproject/archive/2.10.tar.gz 2. 使用VS2015编译 双击pjproject-vs14.sln 为了防止编译报错,首先进入这个目录: pjproject-2.10\p

随机推荐