mysql一层层递归向上直到找到目标

原创

mob64ca12e0c608 2024-09-16 05:35:19 ©著作权

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

MySQL 一层层递归向上直到找到目标的实现

在数据库开发中，有时候我们需要在一棵树状结构（如自引用表）中向上递归查找数据。MySQL 提供了多种方式来实现此功能，这里我将以一个自引用表为例，教你如何通过 SQL 查询实现递归查找目标节点的父节点。

流程概述

以下是实现这一需求的基本步骤：

步骤	描述
1	设计数据库表结构
2	插入测试数据
3	编写递归查询语句
4	执行查询并解析结果
5	总结和扩展应用

步骤详细说明

1. 设计数据库表结构

首先，我们需要设计一个自引用表。假设在这一表中，每个用户都有一个父用户。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES users(id)
);

上述代码定义了一个名为 users 的表，包含三个字段：id（主键）、name（用户名称）和 parent_id（指向父用户的外键）。

2. 插入测试数据

为了测试我们的递归查询，我们向表中插入一些数据。

INSERT INTO users (name, parent_id) VALUES ('Alice', NULL);
INSERT INTO users (name, parent_id) VALUES ('Bob', 1);
INSERT INTO users (name, parent_id) VALUES ('Charlie', 2);
INSERT INTO users (name, parent_id) VALUES ('Diana', 2);
INSERT INTO users (name, parent_id) VALUES ('Eve', 3);

以上代码插入了一些用户数据，构成了一个简单的树状结构：Alice 是根节点，Bob 是 Alice 的子女，Charlie 和 Diana 是 Bob 的子女，而 Eve 是 Charlie 的子女。

3. 编写递归查询语句

在 MySQL 中，可以使用递归公用表表达式（CTE）来向上递归查找父节点，直到找到目标节点。

WITH RECURSIVE parent_cte AS (
    SELECT id, name, parent_id
    FROM users
    WHERE id = 5  -- 假设我们要查找的用户是 Eve，ID 为 5

    UNION ALL

    SELECT u.id, u.name, u.parent_id
    FROM users u
    INNER JOIN parent_cte p ON u.id = p.parent_id
)
SELECT * FROM parent_cte;