mysql 树形查询子数据sql

原创

mob64ca12d52440 2024-08-24 06:29:07 ©著作权

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

MySQL树形查询子数据的实现

在许多应用程序中，我们经常需要处理树形结构的数据，比如组织结构、分类信息等。在MySQL中查询这种层次结构的子数据，并不如单层查询那样简单。因此，本文将详细讲解如何在MySQL中实现树形查询子数据的SQL方式。

整体流程

为了帮助小白开发者理解整个树形查询的步骤，以下是整个过程的详细步骤表：

步骤	描述
1	设计数据库表
2	插入树形结构数据
3	编写获取树形结构的SQL语句
4	使用递归函数查询子节点
5	测试查询结果

步骤详解

步骤1：设计数据库表

首先，我们需要创建一个用于存储树形结构的表。在这个表中，我们会定义一个id，parent_id以及name字段来表示节点之间的关系。

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(255) NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

id: 当前节点的唯一标识。
parent_id: 表示当前节点的父节点，根节点的parent_id为NULL。
name: 节点的名称。

步骤2：插入树形结构数据

接下来，我们可以插入一些示例数据来构建一个树形结构。例如，我们可以插入一个简单的分类树：

INSERT INTO categories (parent_id, name) VALUES
(NULL, 'Electronics'),
(1, 'Televisions'),
(1, 'Mobile Phones'),
(NULL, 'Books'),
(4, 'Fiction'),
(4, 'Non-Fiction');

这里Electronics和Books是根节点，Televisions和Mobile Phones是Electronics的子节点，而Fiction和Non-Fiction是Books的子节点。

步骤3：编写获取树形结构的SQL语句

为了获取特定节点的子数据，我们可以使用递归CTE（公用表表达式）来查询。以下是一个例子，查询Electronics类目的所有子节点。

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE name = 'Electronics'  -- 起始节点

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree;

使用WITH RECURSIVE将查询定义为递归查询。
第一部分选择根节点（此处为Electronics）。
第二部分连接查找所有子节点，通过INNER JOIN连接自身。

步骤4：使用递归函数查询子节点

我们可以将上述查询封装到一个存储过程或函数中，以便复用。以下是一个简单的存储过程，接受一个category_id并返回其所有子节点。

DELIMITER $$

CREATE PROCEDURE GetSubCategories(IN categoryId INT)
BEGIN
    WITH RECURSIVE category_tree AS (
        SELECT id, name, parent_id
        FROM categories
        WHERE id = categoryId
        
        UNION ALL
        
        SELECT c.id, c.name, c.parent_id
        FROM categories c
        INNER JOIN category_tree ct ON ct.id = c.parent_id
    )
    SELECT * FROM category_tree;
END $$

DELIMITER ;

使用DELIMITER命令让SQL解析存储过程中的分号。
GetSubCategories存储过程以categoryId作为参数，并输出对应的子分类。

步骤5：测试查询结果

最后，我们可以通过调用存储过程来测试功能。

CALL GetSubCategories(1); -- 此处1为Electronics的ID

执行此调用将返回Electronics下的所有子分类。

甘特图

为了更好地理解整个过程，可以使用甘特图来展示这些步骤的时间线。这是整个流程的可视化示例。

gantt
    title MySQL树形查询子数据流程
    dateFormat  YYYY-MM-DD
    section 数据库设计
    设计数据库表       :a1, 2023-10-01, 1d
    section 数据插入
    插入核心数据      :a2, 2023-10-02, 1d
    section SQL编写
    编写SQL查询语句     :a3, 2023-10-03, 2d
    section 验证与测试
    测试查询结果        :a4, 2023-10-05, 1d