探索MySQL递归查询：处理层次结构数据

July

发布于 2024-04-15 16:00:59

3360

发布于 2024-04-15 16:00:59

文章被收录于专栏：数据库干货铺数据库干货铺

在数据库管理中，处理具有层次结构的数据一直是一项常见任务。MySQL的递归查询功能通过公用表表达式（CTE）为处理这类数据提供了便捷的方式。递归查询可以用于管理组织结构、目录树等数据，使您能够轻松地查询任意节点的子节点、父节点或整个路径。

1. 语法解释

在MySQL中，递归查询的基本语法结构如下所示：

WITH RECURSIVE cte_name AS (
    -- 初始查询（第一次迭代）
    SELECT initial_query
    UNION ALL
    -- 递归查询（后续迭代）
    SELECT recursive_query
    FROM cte_name
    JOIN base_table ON join_condition
)
-- 最终查询
SELECT * FROM cte_name;

在这个语法中，cte_name 是公用表表达式的名称，initial_query 是初始查询，recursive_query 是递归查询部分，base_table 是要进行递归的基本表，join_condition 是连接条件。

2. 案例演示

下面通过一个实际案例来展示如何在MySQL中利用递归查询处理组织结构数据。假设我们有一个名为employees的表，包含员工的id、姓名和直接上级的id。我们的目标是查询每个员工的直接上级、上级的上级，一直到顶级领导的完整路径。演示的环境为MySQL8.0环境

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 2),
(5, 'Eve', 1);

现在，让我们使用递归查询来获得每个员工的完整上级路径：

WITH RECURSIVE emp_path AS (
    SELECT id, name, 1 as level, CAST(name AS CHAR(200)) as path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, ep.level + 1, CONCAT(ep.path, ' -> ', e.name)
    FROM employees e
    JOIN emp_path ep ON e.manager_id = ep.id
)
SELECT * FROM emp_path;

查询结果如下：

3. MySQL5.7中的实现

在 MySQL 5.7 中，递归查询不支持使用公用表表达式（CTE），而是通过使用用户定义变量（User-Defined Variables）和自连接（Self Join）来实现。虽然这种方法比较繁琐，但仍然可以实现递归查询。例如：

SELECT 
    t1.id as emp_id,
    t1.name as emp_name,
    t1.manager_id as manager_id,
    t1.name as emp_path,
    @pv := t1.manager_id as 'parent_id',
    @path := t1.name as 'path'
FROM 
    employees t1
JOIN 
    (SELECT @pv := '2', @path := '') tmp
WHERE t1.id = @pv

UNION

SELECT 
    t2.id as emp_id,
    t2.name as emp_name,
    t2.manager_id as manager_id,
    CONCAT(@path, ' -> ', t2.name) as emp_path,
    @pv := t2.manager_id as 'parent_id',
    @path := CONCAT(@path, ' -> ', t2.name) as 'path'
FROM 
    employees t2
JOIN 
    (SELECT @pv, @path) tmp
WHERE t2.id = @pv

查询结果如下：