Mysql设计多层级目录表

原创

mob649e816347dd 2024-10-15 04:56:55 ©著作权

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

如何设计 MySQL 多层级目录表

在开发中，设计一个多层级目录结构是很常见的需求。本文将带你逐步了解如何在 MySQL 中实现这一功能。我们将通过创建一个目录表来实现多层级的目录结构。

流程概览

首先，我们需要明确设计的步骤，以下是流程的详细展示：

步骤	描述
1	设计数据库表
2	创建目录表
3	插入数据
4	查询数据

步骤详细说明

1. 设计数据库表

我们需要创建一个目录表，其中包含以下字段：

id: 目录的唯一标识
name: 目录的名称
parent_id: 父目录的 ID，用于建立层级关系

2. 创建目录表

创建表的 SQL 语句如下：

CREATE TABLE directories (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 目录唯一标识
    name VARCHAR(255) NOT NULL,         -- 目录名称
    parent_id INT DEFAULT NULL,          -- 父目录 ID，默认为 NULL
    FOREIGN KEY (parent_id) REFERENCES directories(id)  -- 外键约束
);

上述代码做了什么？

CREATE TABLE directories：创建一个名为 directories 的新表。
id INT AUTO_INCREMENT PRIMARY KEY：定义 id 字段为自增主键。
name VARCHAR(255) NOT NULL：name 字段为目录名称，不能为空。
parent_id INT DEFAULT NULL：parent_id 为父目录的 ID，可以为空。
FOREIGN KEY (parent_id) REFERENCES directories(id)：为 parent_id 添加外键约束，使得它指向同表的 id 字段。

3. 插入数据

接下来，我们可以插入一些示例数据：

INSERT INTO directories (name, parent_id) VALUES ('根目录', NULL);  -- 根目录，没有父目录
INSERT INTO directories (name, parent_id) VALUES ('子目录1', 1);  -- 子目录1，父目录ID为1
INSERT INTO directories (name, parent_id) VALUES ('子目录2', 1);  -- 子目录2，父目录ID为1
INSERT INTO directories (name, parent_id) VALUES ('子目录1.1', 2); -- 子目录1.1，父目录ID为2

这段代码中：

向表中插入了多个目录，并通过 parent_id 字段建立了层级结构。

4. 查询数据

最后，我们可以查询目录的层级结构。例如，获取所有目录及其层级关系的 SQL 语句如下：

SELECT 
    d1.id AS directory_id,
    d1.name AS directory_name,
    d2.name AS parent_directory_name
FROM 
    directories d1
LEFT JOIN 
    directories d2 ON d1.parent_id = d2.id;

这段查询代码的作用是：

使用 LEFT JOIN 将目录表连接到自身，从而获取每个目录的父目录名称。

关系图示例

下面是目录表的 ER 图：

erDiagram
    DIRECTORIES {
        int id PK "目录唯一标识"
        string name "目录名称"
        int parent_id "父目录的ID"
    }
    DIRECTORIES ||--o| DIRECTORIES: "has parent"