MySQL-递归查询

MySQL树形数据递归查询方法
最新推荐文章于 2025-10-17 15:55:23 发布
原创 最新推荐文章于 2025-10-17 15:55:23 发布 · 1.3k 阅读 · 7 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #windows #数据库

背景

相信大家在平时开发的时候都遇见过以下这种树形数据
在这里插入图片描述
这种树形数据如何落库应该这里就不赘述了

核心就是使用额外一个字段parent_id保存父亲节点的id，如下图所示
在这里插入图片描述

这里的classpath指的是当前节点的路径，后续说明其作用

现有需求如下：
1、查询指定id的分类节点的所有子节点
2、查询指定id的分类节点的所有父节点
3、查询整棵分类树，可指定最大层级

常规操作

常规操作就是直接在程序层面控制递归，下面根据需求一 一演示代码。

PS：基础工程代码就不演示了，工程项目代码在评论区链接中获取

查询指定id的分类节点的所有子节点

NormalController

    /**
     * 返回指定nodeId的节点信息，包括所有孩子节点
     * @param nodeId
     * @return
     */
    @GetMapping("/childNodes/{nodeId}")
    public CategoryVO childNodes(@PathVariable("nodeId") Integer nodeId){
        return categoryService.normalChildNodes(nodeId);
    }

CategoryServiceImpl

    @Override
    public CategoryVO normalChildNodes(Integer nodeId) {
        // 查询当前节点信息
        Category category = getById(nodeId);
        return assembleChildren(category);
    }
    private CategoryVO assembleChildren(Category category) {
        // 组装vo信息
        CategoryVO categoryVO = BeanUtil.copyProperties(category, CategoryVO.class);
        // 如果没有子节点了，则退出递归
        List<Category> children = getChildren(category.getId());
        if (children == null || children.isEmpty()) {
            return categoryVO;
        }
        List<CategoryVO> childrenVOs = new ArrayList<>();
        for (Category child : children) {
            // 组装每一个孩子节点
            CategoryVO cv = assembleChildren(child);
            // 将其加入到当前层的孩子节点集合中
            childrenVOs.add(cv);
        }
        categoryVO.setChildren(childrenVOs);
        return categoryVO;
    }
    private List<Category> getChildren(int nodeId) {
        // 如果不存在父亲节点为nodeId的，则说明nodeId并不存在子节点
        return lambdaQuery().eq(Category::getParentId,nodeId).list();
    }

查询id为6的分类信息
在这里插入图片描述

查询指定id的分类节点的所有父节点

NormalController

    /**
     * 返回指定nodeId的节点父级集合，按照从下到上的顺序
     * @param nodeId
     * @return
     */
    @GetMapping("/parentNodes/{nodeId}")
    public List<Category> parentNodes(@PathVariable("nodeId") Integer nodeId){
        return categoryService.normalParentNodes(nodeId);
    }

CategoryServiceImpl

    @Override
    public List<Category> normalParentNodes(Integer nodeId) {
        Category category = getById(nodeId);
        // 找到其所有的父亲节点信息，即根据category的parentId一直查，直到查不到
        List<Category> parentCategories = new ArrayList<>();
        Category current = category;
        while (true) {
            Category parent = lambdaQuery().eq(Category::getId, current.getParentId()).one();
            if (parent == null) {
                break;
            }
            parentCategories.add(parent);
            current = parent;
        }
        return parentCategories;
    }

查询id为12的父级分类信息
在这里插入图片描述

查询整棵分类树，可指定最大层级

NormalController

    /**
     * 返回整棵分类树，可设置最大层级
     * @param maxLevel
     * @return
     */
    @GetMapping("/treeCategory")
    public List<CategoryVO> treeCategory(@RequestParam(value = "maxLevel",required = false) Integer maxLevel){
        return categoryService.normalTreeCategory(maxLevel);
    }

CategoryServiceImpl

    @Override
    public List<CategoryVO> normalTreeCategory(Integer maxLevel) {
        // 虚拟根节点
        CategoryVO root = new CategoryVO();
        root.setId(-1);
        root.setName("ROOT");
        root.setClasspath("/");
        // 队列，为了控制层级的
        Queue<CategoryVO> queue = new LinkedList<>();
        queue.offer(root);
        int level = 1;
        while (!queue.isEmpty()) {
            // 到达最大层级了
            if (maxLevel != null && maxLevel == level) {
                break;
            }
            int size = queue.size();
            for (int i = 0; i < size; i++) {
                CategoryVO poll = queue.poll();
                if (poll == null) {
                    continue;
                }
                //得到当前层级的所有孩子节点
                List<Category> children = getChildren(poll.getId());
                // 有孩子节点
                if (children != null && !children.isEmpty()) {
                    List<CategoryVO> childrenVOs = new ArrayList<>();
                    // 构建孩子节点
                    for (Category child : children) {
                        CategoryVO cv = BeanUtil.copyProperties(child, CategoryVO.class);
                        childrenVOs.add(cv);
                        queue.offer(cv);
                    }
                    // 设置孩子节点
                    poll.setChildren(childrenVOs);
                }
            }
            // 层级自增
            level++;
        }
        // 返回虚拟节点的孩子节点
        return root.getChildren();
    }

查询整棵分类树
在这里插入图片描述
在这里插入图片描述

MySQL8新特性

MySQL8有一个新特性就是with共用表表达式，使用这个特性就可以在MySQL层面实现递归查询。

我们先来看看从上至下的递归查询的SQL语句，查询id为1的节点的所有子节点

WITH recursive r as (
	-- 递归基：由此开始递归
	select id,parent_id,name from category where id = 1
	union ALL
	-- 递归步：关联查询
	select c.id,c.parent_id,c.name
	from category c inner join r 
	-- r作为父表，c作为子表，所以查询条件是c的parent_id=r.id
	where r.id = c.parent_id
)
select id,parent_id,name from r

查询结果如下图所示
在这里插入图片描述

举一反三，则查询id为12的所有父节点信息的就是从下至上的递归查询，SQL如下所示

WITH recursive r as (
	-- 递归基：从id为12的开始
	select id,parent_id,name from category where id = 12
	union ALL
	-- 递归步
	select c.id,c.parent_id,c.name
	from category c inner join r 
	-- 因为是从下至上的查，所以c作为子表，r作为父表
	where r.parent_id = c.id
)
select id,parent_id,name from r

结果如下图所示
在这里插入图片描述

查询指定id的分类节点的所有子节点

AdvancedController

    /**
     * 返回指定nodeId的节点信息，包括所有孩子节点
     * @param nodeId
     * @return
     */
    @GetMapping("/childNodes/{nodeId}")
    public CategoryVO childNodes(@PathVariable("nodeId") Integer nodeId){
        return categoryService.advancedChildNodes(nodeId);
    }

CategoryServiceImpl

    @Override
    public CategoryVO advancedChildNodes(Integer nodeId) {
        List<Category> categories = categoryMapper.advancedChildNodes(nodeId);
        List<CategoryVO> assemble = assemble(categories);
        // 这里一定是第一个，因为categories集合中的是id为nodeId和其子分类的信息，结果assemble组装后，只会存在一个根节点
        return assemble.get(0);
    }
    // 组装categories
    private List<CategoryVO> assemble(List<Category> categories){
        // 组装categories
        CategoryVO root = new CategoryVO();
        root.setId(-1);
        root.setChildren(new ArrayList<>());
        Map<Integer,CategoryVO> categoryMap = new HashMap<>();
        categoryMap.put(-1, root);
        for (Category category : categories) {
            CategoryVO categoryVO = BeanUtil.copyProperties(category, CategoryVO.class);
            categoryVO.setChildren(new ArrayList<>());
            categoryMap.put(category.getId(), categoryVO);
        }
        for (Category category : categories) {
            // 得到自身节点
            CategoryVO categoryVO = categoryMap.get(category.getId());
            // 得到父亲节点
            CategoryVO parent = categoryMap.get(category.getParentId());
            // 没有父亲节点（此情况只会在数据库中最上层节点的父节点id不为-1的时候出现）
            if (parent == null) {
                root.getChildren().add(categoryVO);
                continue;
            }
            parent.getChildren().add(categoryVO);
        }
        return root.getChildren();
    }

CategoryMapper

    <select id="advancedChildNodes" resultType="com.example.mysql8recursive.entity.Category">
        WITH recursive r as (select id, parent_id, name,classpath
                             from category
                             where id = #{nodeId}
                             union ALL
                             select c.id, c.parent_id, c.name,c.classpath
                             from category c
                                      inner join r
                             where r.id = c.parent_id)

        select id, parent_id, name, classpath
        from r
    </select>

查询分类id为6的分类信息
在这里插入图片描述

拓展

这里其实还有另一种利用mybatis的collection子查询的写法，一笔带过

    <resultMap id="BaseResultMap" type="com.example.mysql8recursive.entity.Category">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="parentId" column="parent_id"/>
        <result property="classpath" column="classpath"/>
    </resultMap>
    <resultMap id="CategoryVOResultMap" type="com.example.mysql8recursive.vo.CategoryVO" extends="BaseResultMap">
        <collection property="children"
                    column="id"
                    ofType="com.example.mysql8recursive.vo.CategoryVO"
                    javaType="java.util.ArrayList"
                    select="advancedChildNodes"
        >
        </collection>
    </resultMap>

    <select id="advancedChildNodes" resultMap="CategoryVOResultMap">
        select * from category where parent_id = #{id}
    </select>
查询指定id的分类节点的所有父节点

AdvancedController

    /**
     * 返回指定nodeId的节点父级集合，按照从下到上的顺序
     * @param nodeId
     * @return
     */
    @GetMapping("/parentNodes/{nodeId}")
    public List<Category> parentNodes(@PathVariable("nodeId") Integer nodeId){
        return categoryService.advancedParentNodes(nodeId);
    }

CategorySericeImpl

    @Override
    public List<Category> advancedParentNodes(Integer nodeId) {
        return categoryMapper.advancedParentNodes(nodeId);
    }

CategoryMapper

    <select id="advancedParentNodes" resultType="com.example.mysql8recursive.entity.Category">
        WITH recursive r as (select id, parent_id, name, classpath
                             from category
                             where id = #{nodeId}
                             union ALL
                             select c.id, c.parent_id, c.name, c.classpath
                             from category c
                                      inner join r
                             where r.parent_id = c.id)

        select id, parent_id, name, classpath
        from r
    </select>

查询分类id为12的所有父级分类信息
在这里插入图片描述

查询整棵分类树

AdvancedController

    /**
     * 返回整棵分类树
     * @return
     */
    @GetMapping("/treeCategory")
    public List<CategoryVO> treeCategory(){
        return categoryService.advancedTreeCategory();
    }

CategoryServiceImpl

    @Override
    public List<CategoryVO> advancedTreeCategory() {
        return assemble(list());
    }

查询整棵分类树
在这里插入图片描述
在这里插入图片描述

java1024p
关注
两种mysql递归tree查询效率-mysql递归tree
10-24
### 两种MySQL递归Tree查询效率分析 #### 一、背景与目的 在数据库操作中，经常需要处理具有层级结构的数据。例如，在处理组织结构、文件系统或是地区划分时，通常会采用递归的方式来查询这些层级关系。MySQL作为...
MySQL递归查询
07-15
MySQL递归查询，oracle在迁移成mysql后难免会遇到的问题。
mysql 递归查询
骑行大地的专栏
06-30 2845
转自：http://www.cnblogs.com/xiaoxi/p/5942805.html 作者语：之前项目使用的是ms sqlserver数据库，支持with as 语法中直接递归调用。但mysql并不支持with as 语法，只能将with as的过程封装成函数，然后在查询中进行调用。此文有效的解决了我的问题，特转载至此，以留以后查询使用。 1、创建表： DROP TABLE I
mysql递归查询所有子节点，高效地查询任意节点的所有子节点
2503_90648021的博客
10-17 265
在 MySQL 中，可以使用来查询某个节点的所有子节点（包括多级子节点）。这种方式适用于 MySQL 8.0 及以上版本（支持 CTE）。
mysql以及oracle的递归查询
yangnianbing110的专栏
07-03 8689
在oracle中实现递归查询的途径jiaoduo
mysql-installer-community-8.0.39.0安装包
09-18
版本号8.0是MySQL的重要更新，提供了许多新特性和改进，如对JSON的增强支持、窗口函数、递归查询、隐藏密码功能、优化器改进等。 MySQL Installer中的Community版本为用户提供了一个免费的下载和安装选项，使得用户...
mysql-server-mysql-5.7.36.tar.gz
09-16
5. **柱状图索引（CTE）**：引入了Common Table Expressions（CTE），这使得复杂的递归查询和临时结果集的处理变得更加简单。 6. **分区表优化**：对分区表的处理有了显著的提升，包括更灵活的分区策略和更高效的...
MySQL5.7 递归查询 看这一篇就够了 简单易懂
weixin_54138140的博客
09-05 2898
MySQL5.7 递归查询 看这一篇就够了 简单易懂
mysql递归查询
热门推荐
dear_Alice_moon的专栏
11-16 1万+
Mysql递归调用
mysql8递归查询
最新发布
11-16
<think> 我们参考了用户提供的引用内容，特别是引用[1]和引用[2]都提到了MySQL 8的递归查询使用`WITH RECURSIVE`语法。用户明确要求MySQL 8中进行递归查询的方法，因此我们将重点介绍`WITH RECURSIVE`的用法。 递归查询通常用于处理树形结构数据，比如菜单、地区、组织架构等。在MySQL 8之前，递归查询需要通过存储过程或函数来实现，而MySQL 8引入了通用表表达式（CTE）的递归功能，使得递归查询更加简便。 递归查询的基本结构包括两个部分： 1. 递归基（初始查询）：这是递归的起点，选择初始记录。 2. 递归步（递归部分）：通过连接递归基（或前一步递归的结果）和表本身，不断迭代，直到满足终止条件。 语法格式： ```sql WITH RECURSIVE 临时表名 AS ( -- 递归基（初始查询） SELECT ... FROM 表名 WHERE 初始条件 UNION ALL -- 递归步（递归部分） SELECT ... FROM 表名 JOIN 临时表名 ON 连接条件 WHERE 终止条件（可选） ) SELECT * FROM 临时表名; ``` 注意：递归查询必须使用UNION ALL（允许重复）或者UNION（去重），并且递归步中不能使用聚合函数、窗口函数、GROUP BY等。 下面我们通过两个例子来说明： 1. 自底向上（查询某个节点的所有父节点）：例如查询id=12的所有父节点。 2. 自顶向下（查询某个节点的所有子节点）：例如查询id=1的所有子节点。 假设我们有一个表`category`，包含字段`id`, `parent_id`, `name`。 例子1：查询id为12的所有父节点（包括自身？根据需求，通常不包括自身，但也可以包括，这里我们根据引用[1]的示例，它包括了自身，因为初始查询包含了id=12的记录，然后向上递归） 引用[1]中的例子是查询id=12的所有父节点，它包括了自身（因为从id=12开始），如果我们不希望包括自身，可以在最后的结果中过滤掉，或者在递归基中不选择它（但这样递归就会从它的父节点开始，所以通常包括自身再过滤）。 以下是引用[1]中的示例（包括自身）： ```sql WITH RECURSIVE r AS ( -- 递归基：从id为12的记录开始 SELECT id, parent_id, name FROM category WHERE id = 12 UNION ALL -- 递归步：通过连接当前递归结果（r）和原表（c），找到r中记录的父节点 SELECT c.id, c.parent_id, c.name FROM category c INNER JOIN r ON r.parent_id = c.id ) SELECT * FROM r; ``` 注意：这个查询会包括id=12的节点，以及它的所有父节点。如果不想包括自身，可以在最后加上`WHERE id <> 12`。 例子2：查询id为1的所有子节点（包括自身？同样，递归基中选择了id=1，所以包括自身，如果不想要，同样可以过滤） ```sql WITH RECURSIVE r AS ( -- 递归基：从id为1的记录开始 SELECT id, parent_id, name FROM category WHERE id = 1 UNION ALL -- 递归步：找到r中所有记录的子节点（注意连接方向） SELECT c.id, c.parent_id, c.name FROM category c INNER JOIN r ON c.parent_id = r.id ) SELECT * FROM r; ``` 这里递归步的连接条件是`c.parent_id = r.id`，表示找r中每个节点的直接子节点。 注意事项： 1. 递归深度：MySQL默认限制了递归深度（通过系统变量`cte_max_recursion_depth`，默认1000），如果递归层数超过这个值，会报错。可以通过设置会话变量来调整，例如：`SET SESSION cte_max_recursion_depth = 10000;`。 2. 避免无限循环：在递归查询中，如果数据有循环（比如A的父节点是B，而B的父节点又是A），则递归会一直进行直到达到最大深度。为了避免这种情况，可以在递归步中加入条件来终止循环（例如，通过记录路径来检查是否重复）。 引用[3]中展示了一种不使用`WITH RECURSIVE`的递归查询方法，它使用了变量和GROUP_CONCAT函数，但这种方法较为复杂，且依赖于特定的变量赋值顺序，在MySQL 8中不推荐使用。 因此，我们推荐使用MySQL 8的`WITH RECURSIVE`语法来实现递归查询。 生成相关问题： 1. 如何避免递归查询中的无限循环？ 2. 递归查询的性能如何优化？ 3. 除了递归查询，还有其他方法处理树形结构数据吗？ </think> ### MySQL 8递归查询的实现方法 MySQL 8通过`WITH RECURSIVE`语法实现递归查询，主要用于处理树形结构数据（如菜单、地区、组织架构等）。以下是具体实现方法和示例： --- #### 1. **基本语法结构** ```sql WITH RECURSIVE 临时表名 AS ( -- 初始查询（递归起点） SELECT ... UNION ALL -- 递归查询（连接父/子节点） SELECT ... FROM 表名 JOIN 临时表名 ON 连接条件 ) SELECT * FROM 临时表名; ``` --- #### 2. **两种典型场景** ##### **场景1：查询所有子节点（自上而下）** **示例**：查询ID=1的所有子孙节点（含自身） ```sql WITH RECURSIVE sub_tree AS ( -- 初始查询：起点ID=1 SELECT id, name, parent_id FROM category WHERE id = 1 UNION ALL -- 递归查询：连接子节点 SELECT c.id, c.name, c.parent_id FROM category c JOIN sub_tree st ON c.parent_id = st.id ) SELECT * FROM sub_tree; ``` **说明**： - 从根节点`id=1`开始 - 递归步通过`c.parent_id = st.id`向下查找子节点 ##### **场景2：查询所有父节点（自下而上）** **示例**：查询ID=12的所有祖先节点（含自身） ```sql WITH RECURSIVE parent_tree AS ( -- 初始查询：起点ID=12 SELECT id, name, parent_id FROM category WHERE id = 12 UNION ALL -- 递归查询：连接父节点 SELECT c.id, c.name, c.parent_id FROM category c JOIN parent_tree pt ON pt.parent_id = c.id ) SELECT * FROM parent_tree; ``` **说明**： - 从叶节点`id=12`开始 - 递归步通过`pt.parent_id = c.id`向上查找父节点[^1][^2] --- #### 3. **关键注意事项** 1. **终止条件**： - 隐式终止：当递归步不再产生新结果时自动停止 - 显式终止：添加终止条件（如`WHERE level < 5`限制深度） 2. **递归深度限制**： - 默认最大递归深度为`1000` - 修改深度：`SET SESSION cte_max_recursion_depth = 10000;` 3. **避免循环引用**： - 若数据存在环（如A→B→A），需添加路径检查： ```sql AND c.id NOT IN (SELECT id FROM parent_tree) -- 防止重复 ``` --- #### 4. **性能优化建议** - **索引优化**：确保`parent_id`字段有索引 - **限制字段**：仅选择必要字段（避免`SELECT *`） - **深度控制**：通过`WHERE level <= N`限制递归层数[^2] --- #### 5. **与旧版本对比** | 方法 | MySQL 5.x及之前 | MySQL 8 | |---------------------|----------------------|----------------------| | **实现方式** | 存储过程/函数 | `WITH RECURSIVE` | | **可读性** | 复杂 | 简洁直观 | | **执行效率** | 较低 | 更高 | | **维护难度** | 高 | 低 | > ⚠️ 旧版本需用存储过程实现递归，代码复杂且效率低下[^3]。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值