mysql 查询某个部门下所有的子级部门

作者： 分类：所有,mysql 字数：7352

MySQL 查询某个部门下所有的子级部门

一、背景与问题

在企业级应用中，部门组织结构通常以树形结构存在。例如，一个公司可能存在"研发部"作为根节点，其下包含"前端组"、"后端组"等子部门，而每个子部门又可能包含更细粒度的子部门。这种层级关系需要支持以下典型查询场景：

  • 查询某个部门下所有直接子部门（一阶）
  • 查询某个部门下所有间接子部门（二阶、三阶等）
  • 查询某个部门下的所有下属部门（包括所有层级）

传统关系型数据库中，这种树形结构通常采用邻接列表模型（Adjacency List Model）实现，即每个节点记录其父节点ID。这种结构虽然实现简单，但要实现递归查询时会面临性能和逻辑复杂度的挑战。

二、基本原理

在邻接列表模型中，部门表通常包含以下字段：

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT,
    INDEX idx_parent (parent_id)
);

查询子部门的核心原理是：

  1. 从目标部门开始，找到其直接子部门（parent_id = 目标id）
  2. 对每个直接子部门，递归查找其子部门
  3. 直到没有更多子部门为止

这种递归查询在传统MySQL版本中需要借助存储过程实现，而在MySQL 8.0+支持CTE（Common Table Expressions）后，可以使用递归查询直接实现。

三、环境准备

-- 创建部门表
CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT,
    INDEX idx_parent (parent_id)
);

-- 插入测试数据（部门结构为：技术部 -> 前端组 -> 前端开发、前端测试；后端组 -> 后端开发）
INSERT INTO department (id, name, parent_id) VALUES
(1, '技术部', NULL),
(2, '前端组', 1),
(3, '前端开发', 2),
(4, '前端测试', 2),
(5, '后端组', 1),
(6, '后端开发', 5);

四、核心实现

1. MySQL 8.0+ 使用CTE递归查询

WITH RECURSIVE dept_tree AS (
    -- 初始查询：获取起始部门
    SELECT id, name, parent_id
    FROM department
    WHERE id = 2  -- 查询前端组下的所有子部门
    
    UNION ALL
    
    -- 递归查询：查找所有子部门
    SELECT d.id, d.name, d.parent_id
    FROM department d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
-- 最终结果
SELECT * FROM dept_tree;

关键代码解释：

  • WITH RECURSIVE 定义递归查询块
  • 第一层查询获取起始节点（前端组）
  • 第二层查询通过INNER JOIN找到所有子节点
  • UNION ALL将初始结果和递归结果合并

执行结果：

+----+--------------+----------+
| id | name         | parent_id |
+----+--------------+----------+
|  2 | 前端组       |        1  |
|  3 | 前端开发     |        2  |
|  4 | 前端测试     |        2  |
+----+--------------+----------+

2. MySQL 5.7 使用存储过程实现

DELIMITER //
CREATE PROCEDURE get_sub_departments(IN start_id INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE current_id INT;
    DECLARE cur CURSOR FOR
        SELECT id FROM department WHERE parent_id = current_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_sub_departments (
        id INT PRIMARY KEY
    );
    
    -- 初始化
    INSERT INTO temp_sub_departments SELECT id FROM department WHERE id = start_id;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO current_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 插入当前层级的子部门
        INSERT INTO temp_sub_departments
        SELECT id FROM department WHERE parent_id = current_id;
        
        -- 递归查询下一层级
        SET current_id = (SELECT id FROM temp_sub_departments WHERE id = current_id);
    END LOOP;
    
    SELECT * FROM temp_sub_departments;
    DROP TEMPORARY TABLE temp_sub_departments;
END //
DELIMITER ;

-- 调用存储过程
CALL get_sub_departments(2);

关键代码解释：

  • 使用游标逐层遍历部门
  • 通过临时表存储中间结果
  • 递归查找下一层级的子部门
  • 通过parent_id进行层级关联

3. 使用闭包表（Closure Table）优化查询

-- 创建闭包表
CREATE TABLE department_closure (
    ancestor_id INT,
    descendant_id INT,
    PRIMARY KEY (ancestor_id, descendant_id)
);

-- 初始化闭包表（批量插入）
INSERT INTO department_closure (ancestor_id, descendant_id)
SELECT d1.id, d2.id
FROM department d1
JOIN department d2 ON d1.id = d2.parent_id
UNION
SELECT d.id, d.id
FROM department d;

-- 查询某个部门下所有子部门
SELECT d2.id, d2.name
FROM department_closure dc
JOIN department d2 ON dc.descendant_id = d2.id
WHERE dc.ancestor_id = 2;

关键代码解释：

  • 闭包表记录所有祖先与后代的关系
  • UNION包含自身（即自己是自己的祖先）
  • 查询时只需查找特定祖先的所有后代

五、完整案例

1. 创建测试数据

-- 创建部门表
CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT,
    INDEX idx_parent (parent_id)
);

-- 插入测试数据
INSERT INTO department (id, name, parent_id) VALUES
(1, '技术部', NULL),
(2, '前端组', 1),
(3, '前端开发', 2),
(4, '前端测试', 2),
(5, '后端组', 1),
(6, '后端开发', 5),
(7, '移动端开发', 3),
(8, '移动端测试', 3);

2. 查询前端组下所有子部门

WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id
    FROM department
    WHERE id = 2
    
    UNION ALL
    
    SELECT d.id, d.name, d.parent_id
    FROM department d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

执行结果：

+----+--------------+----------+
| id | name         | parent_id |
+----+--------------+----------+
|  2 | 前端组       |        1  |
|  3 | 前端开发     |        2  |
|  4 | 前端测试     |        2  |
|  7 | 移动端开发   |        3  |
|  8 | 移动端测试   |        3  |
+----+--------------+----------+

六、源码解析

以CTE递归查询为例，逐层分析代码结构：

  1. 递归查询块定义：

    WITH RECURSIVE dept_tree AS (
    -- 初始查询
    SELECT ... 
    UNION ALL
    -- 递归查询
    SELECT ...
)

  2. 初始查询：

    SELECT id, name, parent_id
FROM department
WHERE id = 2

    这部分获取起始部门（前端组）的信息。

  3. 递归查询：

    SELECT d.id, d.name, d.parent_id
FROM department d
INNER JOIN dept_tree dt ON d.parent_id = dt.id

    通过INNER JOIN将当前层级的子部门与递归结果关联，形成新的查询结果集。

  4. 最终结果：

    SELECT * FROM dept_tree;

    返回所有层级的部门信息。

七、进阶使用

1. 查询特定深度的子部门

WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id, 0 AS level
    FROM department
    WHERE id = 2
    
    UNION ALL
    
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM department d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree
WHERE level <= 2;  -- 查询一阶和二阶子部门

2. 查询包含子部门的部门总数

SELECT COUNT(*) AS total
FROM department
WHERE id IN (
    WITH RECURSIVE dept_tree AS (
        SELECT id
        FROM department
        WHERE id = 2
        
        UNION ALL
        
        SELECT d.id
        FROM department d
        INNER JOIN dept_tree dt ON d.parent_id = dt.id
    )
    SELECT id FROM dept_tree
);

八、性能与工程实践

1. 性能优化策略

优化策略说明
索引优化在parent_id字段上创建索引（已默认创建）
限制查询深度使用WHERE level <= N限制递归深度
闭包表优化预处理生成闭包表，避免每次查询时递归计算
分页处理使用LIMIT和OFFSET避免一次性获取大量数据

2. 安全风险分析

  • SQL注入风险：如果使用拼接SQL的方式，需要确保参数化查询
  • 权限控制：需要确保用户只能查询其有权限访问的部门
  • 数据一致性：在修改部门关系时，需要考虑闭包表的维护

3. 异常处理

  • 防止无限递归：通过MAX_RECURSION限制最大递归深度
  • 处理空值：确保parent_id字段允许NULL值
  • 锁表风险：在更新部门关系时考虑事务和锁机制

九、常见问题与踩坑

1. 错误示例：忘记处理根节点

-- 错误：未包含起始部门本身
WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id
    FROM department
    WHERE parent_id = 2
    ...
)

问题：起始部门本身不会被包含在结果中，导致遗漏

改进：在初始查询中包含起始部门

2. 错误示例：未处理子查询中的循环

-- 错误：可能导致无限循环
SELECT d.id, d.name
FROM department d
JOIN dept_tree dt ON d.parent_id = dt.id

问题：存在循环引用时会导致查询超时

改进：在递归查询中添加WHERE level < MAX_LEVEL限制

3. 错误示例：未处理索引失效

-- 错误：未为parent_id创建索引
SELECT d.id, d.name
FROM department d
JOIN dept_tree dt ON d.parent_id = dt.id

问题：未创建索引会导致全表扫描

改进：确保parent_id字段有索引

十、最佳实践

1. 推荐方案

  • MySQL 8.0+：优先使用CTE递归查询，简单易维护
  • MySQL 5.7：使用存储过程实现，但需要处理临时表和游标
  • 频繁查询场景：使用闭包表，预处理生成所有祖先-后代关系

2. 使用建议

  • 数据量小：直接使用递归查询
  • 数据量大：使用闭包表或分页查询
  • 需要实时数据：使用CTE，但注意索引优化
  • 需要历史数据：使用闭包表存储历史变更记录

3. 编码规范

  • 使用参数化查询防止SQL注入
  • 在查询中添加WHERE level <= N限制深度
  • 对于闭包表，定期更新以保持数据一致性
  • 使用事务处理部门关系的修改操作

十一、总结

MySQL查询部门下所有子级部门的解决方案需要根据具体场景选择合适的方法。对于简单的树形结构，CTE递归查询是最直接的选择，但需要考虑索引优化和递归深度限制。对于复杂场景，闭包表提供了更好的性能，但需要预先处理数据。实际开发中，应根据数据量、查询频率、实时性要求等因素综合选择方案。同时，要特别注意SQL注入、循环引用、索引失效等常见问题，通过合理的索引、分页、事务控制等手段确保系统稳定运行。

Mysql , sql
最后修改于：2026年09月18日 14:55

评论已关闭

推荐阅读

AIGC实战——Transformer模型
2024年12月01日
python , AI , 机器学习
Socket TCP 和 UDP 编程基础（Python）
2024年11月30日
python , tcp , udp
如何使用 ChatGPT 进行学术润色？你需要这些指令
2024年12月01日
AI
最新 Python 调用 OpenAi 详细教程实现问答、图像合成、图像理解、语音合成、语音识别（详细教程）
2024年11月24日
python , AI
ChatGPT 和 DALL·E 2 配合生成故事绘本
2024年12月01日
python , AI
omegaconf，一个超强的 Python 库！
2024年11月24日
python
【视觉AIGC识别】误差特征、人脸伪造检测、其他类型假图检测
2024年12月01日
python , AI
［超级详细］如何在深度学习训练模型过程中使用 GPU 加速
2024年11月29日
python , AI , 机器学习
Python 物理引擎pymunk最完整教程
2024年11月27日
python
MediaPipe 人体姿态与手指关键点检测教程
2024年11月27日
python
深入了解 Taipy：Python 打造 Web 应用的全面教程
2024年11月26日
python
基于Transformer的时间序列预测模型
2024年11月25日
python , Matplotlib , 机器学习
Python在金融大数据分析中的AI应用（股价分析、量化交易）实战
2024年11月25日
python , Matplotlib , 机器学习
AIGC Gradio系列学习教程之Components
2024年12月01日
python , AI
Python3 `asyncio` — 异步 I/O，事件循环和并发工具
2024年11月30日
python
llama-factory SFT系列教程：大模型在自定义数据集 LoRA 训练与部署
2024年12月01日
python , AI , 机器学习
Python 多线程和多进程用法
2024年11月24日
python
Python socket详解，全网最全教程
2024年11月27日
python , http , websocket
python之plot()和subplot()画图
2024年11月26日
python , Matplotlib
理解 DALL·E 2、Stable Diffusion 和 Midjourney 工作原理
2024年12月01日
python , AI , 机器学习
Copyright © 2026 Tech Blog
All Right Reserved | 京ICP备19053165号