mysql 根据父子结构获取层级

原创

mob649e816209c2 2024-09-24 05:57:25 ©著作权

文章标签 MySQL 数据 Business 文章分类 MySQL 数据库

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

使用 MySQL 获取父子结构的层级关系

在很多数据库应用中，我们常常需要处理树形结构的数据，比如菜单、分类等。用 MySQL 来实现根据父子结构获取层级关系是一个常见的需求。本文将详细介绍如何通过步骤和代码来完成这一任务，适合刚入行的小白开发者。

整体流程

在开始之前，我们需要明确整个操作的流程：

步骤	描述
1	创建 MySQL 数据表
2	插入示例数据
3	编写 SQL 查询
4	处理结果

接下来，我们将逐步进行每一项操作，帮助你理解具体的实现过程。

第一步：创建 MySQL 数据表

首先，我们需要创建一个存储父子结构的表。我们以一个简单的分类表为例，表结构如下：

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

解释：

id: 分类的唯一标识符。
name: 分类名称。
parent_id: 指向父分类的外键，根分类的 parent_id 为空。

第二步：插入示例数据

接下来，我们插入一些示例数据来测试我们的查询。这里我们插入一些带有层级关系的分类数据：

INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Laptops', 1),
('Desktops', 1),
('Cameras', 1),
('Smartphones', 1),
('Gaming Laptops', 2),
('Business Laptops', 2);

解释：

在上述操作中，我们创建了一个包含电子产品及其子分类的类别结构，比如“Laptops”（笔记本电脑）是“Electronics”（电子产品）的子类别。

第三步：编写 SQL 查询

现在我们已经有了表和数据，接下来就可以编写 SQL 查询来获取层级结构的信息。我们可以使用递归查询（看你的 MySQL 版本是否支持CTE）来实现这一点：

WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ch.level + 1
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM category_hierarchy ORDER BY level;

解释：

WITH RECURSIVE: 定义一个递归的公共表表达式（CTE）。
第一个查询选择所有顶层分类（没有父分类）。
第二个查询从当前层级的分类再查找它们的子分类，并增加层级计数。
最后，从递归查询中选择所有结果并按层级排序。

第四步：处理结果

执行以上查询后，你将会获得一份包含所有分类及其层级的结果。结果会显示分类的 ID、名称和对应的层级（level）。你可以根据需要进一步处理这些结果，比如将其转化为 JSON 格式以便在前端展示。

结果示例：

id	name	parent_id	level
1	Electronics	NULL
2	Laptops	1	1
6	Gaming Laptops	2	2
7	Business Laptops	2	2
3	Desktops	1	1
4	Cameras	1	1
5	Smartphones	1	1

状态图

为了帮助更好地理解层级结构，可以用状态图表示不同层级分类间的关系，示例代码如下：

stateDiagram
    state top {
        [*] --> Electronics
        Electronics --> Laptops
        Electronics --> Desktops
        Electronics --> Cameras
        Electronics --> Smartphones
        Laptops --> Gaming_Laptops
        Laptops --> Business_Laptops
    }