暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 探索MySQL递归查询：处理层次结构数据

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

数据库干货铺 2024-04-09
462

在数据库管理中，处理具有层次结构的数据一直是一项常见任务。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


          查询结果如下：

           这个查询通过使用用户定义变量 @pv
          和 @path
          来保存父级的 ID 和路径，然后通过自连接不断迭代地找到每个员工的直接上级以及完整的上级路径。注意这是一种近似的实现，可能不如 CTE 那样直观和简洁。

          当然如果需求比较简单的递归也可以用其他方式实现，具体看表设计情况及数据层级关系而编写脚本。


          4. 递归查询原理与使用场景

              递归查询通过迭代处理分层数据的结果集来实现。在我们的案例中，初始查询选择了顶级领导，递归查询则利用较小层级结果，通过连接操作找到下一层级的员工，持续迭代直至到达最底层。递归查询每次迭代都使用前一次结果作为输入，从而构建完整的层级关系。

              递归查询的关键在于设计良好的初始查询和递归查询部分，以确保每次迭代都能准确找到下一层数据并连接到前一次的结果。

              通过递归查询，可以轻松处理树形数据结构，解决组织结构、目录树等具有分层关系的数据问题，为数据分析提供了便利。

              递归查询在实际应用中还能快速准确地分析和查找复杂层级数据关系，提升数据处理效率和准确性。

          希望这篇文章能帮助您了解MySQL中的递归查询，以及如何利用这一功能处理层次结构数据。


          递归mysqlselect
          文章转载自数据库干货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

          评论