mysql 父子表递归

原创

mob64ca12de62a6 2024-09-05 03:18:07 ©著作权

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

MySQL 父子表递归的实现

在数据库设计中，父子表关系常用于存储层次结构数据，例如分类目录或组织架构。实现 MySQL 中的父子表递归查询可以帮助我们获取层级关系中的所有子元素。接下来，我们将逐步了解整件事情的流程和实现代码。

一、流程说明

为了实现父子表递归查询，我们进行以下步骤：

步骤	描述
1	创建父子表（例如，分类表）
2	插入样本数据
3	编写递归查询
4	执行查询并显示结果

二、步骤详解

1. 创建父子表

首先，我们需要创建一个简单的表结构，该表包含父级和子级的关系。

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键
    name VARCHAR(255) NOT NULL,          -- 分类名称
    parent_id INT DEFAULT NULL,           -- 父级ID，根分类的父级ID为NULL
    FOREIGN KEY (parent_id) REFERENCES categories(id) -- 设定外键
);

上述代码创建了一个分类表，其中 parent_id 是用来标识父分类的字段。

2. 插入样本数据

接下来，向表中插入一些示例数据，以构建分类层级结构。

INSERT INTO categories (name, parent_id) VALUES 
('Electronics', NULL),  -- 根分类
('Computers', 1),       -- 子分类，父级是 'Electronics'
('Laptops', 2),         -- 孙分类，父级是 'Computers'
('Desktops', 2),        -- 孙分类，父级是 'Computers'
('Smartphones', 1),     -- 子分类，父级是 'Electronics'
('Cameras', 1);         -- 子分类，父级是 'Electronics'

上述代码将多个分类插入到表中，形成一个简单的层级结构。

3. 编写递归查询

使用 MySQL 的递归查询功能（仅限某些版本）可以帮助我们获取整个层级数据。以下是查询的代码：

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL  -- 获取根分类
    UNION ALL
    SELECT c.id, c.name, c.parent_id FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id  -- 连接到子分类
)
SELECT * FROM category_tree;  -- 获取层级结构

上述代码首先选择根分类，然后递归地连接到所有子分类，最终返回整个层级结构。

4. 执行查询并显示结果

最后，我们运行上述查询并查看结果。执行时，在终端或 SQL 客户端运行查询。

-- 运行查询
SELECT * FROM category_tree;

执行后，您将会看到一个包含所有分类及其层级关系的结果集。

三、序列图展示

以下是一个简单的序列图，展示了父子查询的过程。

sequenceDiagram
    participant User
    participant DB
    User->>DB: 发送创建表的请求
    DB-->>User: 表结构创建完成
    User->>DB: 插入样本数据
    DB-->>User: 数据插入完成
    User->>DB: 发起递归查询
    DB-->>User: 返回分类层级结构