mysql 递归|极客笔记

mysql 递归

MySQL 是一种开源的关系型数据库管理系统，被广泛用于Web应用程序的开发中。在实际的数据处理中，经常会遇到需要使用递归查询来处理某些复杂的数据结构或业务逻辑。本文将详细介绍在MySQL中如何使用递归查询。

递归查询概述

递归查询是指在查询过程中需要通过多次迭代计算来获取最终结果的一种查询方法。在MySQL中，没有内建的递归函数，但是我们可以使用一些技巧来实现递归查询。

常见的情况是对于树状结构的数据进行递归查询，例如组织机构图、文件目录结构等。在这些情况下，我们需要递归地沿着父子关系或者路径找到所有相关的节点。

使用递归查询树状结构

创建示例数据表

为了说明递归查询的使用，首先我们创建一个简单的树状结构的数据表。假设我们有一个表 department，其中存储了部门的信息，包括部门ID和上级部门ID：

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

INSERT INTO department (id, name, parent_id) VALUES
(1, '总部', NULL),
(2, '财务部', 1),
(3, '技术部', 1),
(4, '人事部', 1),
(5, '财务一部', 2),
(6, '财务二部', 2),
(7, '技术一部', 3),
(8, '技术二部', 3),
(9, '技术一组', 7),
(10, '技术二组', 8);

以上是一个简单的部门信息表，每个部门包括一个唯一的 id，部门名称 name，以及上级部门的 parent_id。接下来我们将使用递归查询来查询部门的层级关系。

递归查询部门层级

使用递归查询的一个常见方法是结合MySQL的用户定义变量（User Defined Variables）来模拟递归过程。下面是一个示例查询，查询部门的全路径：

SELECT 
    GROUP_CONCAT(name ORDER BY level DESC SEPARATOR ' / ') AS full_path
FROM (
    SELECT 
        id,
        name,
        parent_id,
        0 AS level
    FROM department
    WHERE id = 10
    UNION ALL
    SELECT 
        d.id,
        d.name,
        d.parent_id,
        rd.level + 1
    FROM department d
    JOIN (
        SELECT 
            id,
            parent_id,
            level
        FROM department
        WHERE id = 10
        UNION ALL
        SELECT 
            rd.id,
            d.parent_id,
            rd.level + 1
        FROM department d
        JOIN recursive_department rd ON d.id = rd.parent_id
    ) rd ON d.id = rd.parent_id
) AS recursive_department;

在上面的查询中，我们首先选取了 id = 10 的部门作为递归的起点，然后通过 UNION ALL 将递归查询结果集进行递归迭代，直到遍历完整个部门的层级关系。最后通过 GROUP_CONCAT 函数将部门的全路径连接在一起。

运行上面的查询，我们可以得到部门 技术二组 的全路径信息为 总部 / 技术部 / 技术二部 / 技术二组。

使用递归查询获取部门下所有子部门

除了查询部门的全路径外，有时候我们还需要查询部门下的所有子部门。以下是一个查询部门 财务部 下所有子部门的示例：

WITH RECURSIVE sub_departments AS (
    SELECT 
        id,
        name,
        parent_id
    FROM department
    WHERE name = '财务部'
    UNION ALL
    SELECT 
        d.id,
        d.name,
        d.parent_id
    FROM department d
    JOIN sub_departments sd ON d.parent_id = sd.id
)
SELECT 
    id,
    name
FROM sub_departments;

在上面的查询中，使用 WITH RECURSIVE 关键字声明了一个递归公共表达式，实现了获取部门 财务部 下所有子部门的递归查询。最终我们将得到所有子部门的 id 和 name。