mysql层级递归查询

原创

mob64ca12eea322 2023-08-25 10:06:38 ©著作权

文章标签 递归查询 mysql 递归 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12eea322的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql层级递归查询

简介

在数据库设计中，常常需要处理层级关系的数据，比如组织结构、分类目录等。层级递归查询是一种常用的查询方法，可以通过一条SQL语句递归地查询出某个节点及其所有子节点。

本文将介绍mysql中层级递归查询的原理、使用方法，并提供代码示例。

原理

在mysql中，可以使用WITH RECURSIVE关键字来实现层级递归查询。WITH RECURSIVE语句可以视为一个递归的公共表表达式（Recursive Common Table Expression，RCTE），它可以定义一个或多个递归的子查询，并将其结果作为表使用。

层级递归查询通常需要两个步骤：

定义递归查询的初始条件和递归关系。
对递归查询进行迭代，直到满足终止条件。

示例

以一个简单的组织结构为例，我们有一个员工表，每个员工有唯一的ID，以及上级员工的ID（如果没有上级，则上级员工ID为NULL）。我们希望查询出某个员工及其所有下属员工。

首先，我们需要创建一个示例表，并插入一些数据：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  supervisor_id INT,
  FOREIGN KEY (supervisor_id) REFERENCES employees(id)
);

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

接下来，我们可以使用WITH RECURSIVE语句进行层级递归查询：

WITH RECURSIVE subordinates AS (
  SELECT id, name, supervisor_id
  FROM employees
  WHERE id = 2 -- 查询ID为2的员工及其下属员工
  UNION ALL
  SELECT e.id, e.name, e.supervisor_id
  FROM employees e
  INNER JOIN subordinates s ON e.supervisor_id = s.id
)
SELECT * FROM subordinates;

这段代码的含义是：首先选取ID为2的员工作为初始条件，然后通过递归关系查询出其下属员工，最终返回所有查询结果。

上述代码将返回ID为2的员工Alice及其所有下属员工的信息。

图示

以下是上述组织结构的关系图：

erDiagram
    employees {
        int id
        varchar(100) name
        int supervisor_id
        foreign key (supervisor_id) references employees(id)
    }