MySQL实现上下级关联的方法简介（MySQL上下关联）

MySQL实现上下级关联的方法简介

在开发一个具有层级关系的系统时，如员工-部门的管理系统、栏目-文章的管理系统等，经常需要使用到上下级关联，也就是父子关系。在MySQL中，实现上下级关联有多种方法，常见的有嵌套集合模型、路径模型和材料化路径模型等。下面我们将对它们进行简单介绍。

1. 嵌套集合模型

嵌套集合模型是一种经典的层次模型，它用一种树结构将数据组织起来，每个节点都包含其子节点的信息，并且可以使用递归查询来实现对整个树形结构的查询。下面是一个示例表：

CREATE TABLE categories (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

parent_id INT,

lft INT NOT NULL,

rgt INT NOT NULL,

depth INT NOT NULL,

UNIQUE KEY(name)

);

其中，lft和rgt两列用于存储节点在树形结构中的左右边界，depth用于表示节点的深度（从0开始）。插入数据时，需要通过递归方式计算出lft、rgt和depth的值：

INSERT INTO categories (name, parent_id, lft, rgt, depth)

VALUES ( Music , NULL, 1, 20, 0);

INSERT INTO categories (name, parent_id, lft, rgt, depth)

VALUES ( Pop , 1, 2, 7, 1);

INSERT INTO categories (name, parent_id, lft, rgt, depth)

VALUES ( Rock , 1, 8, 19, 1);

INSERT INTO categories (name, parent_id, lft, rgt, depth)

VALUES ( Classic , 2, 3, 6, 2);

INSERT INTO categories (name, parent_id, lft, rgt, depth)

VALUES ( Metal , 3, 4, 5, 3);

INSERT INTO categories (name, parent_id, lft, rgt, depth)

VALUES ( Indie , 8, 9, 10, 2);

查询某个节点的子节点可以使用如下查询语句：

SELECT * FROM categories WHERE parent_id = ? ORDER BY lft;

递归查询可以使用MySQL中的WITH RECURSIVE语句：

WITH RECURSIVE cte AS (

SELECT id, name, parent_id, lft, rgt, depth

FROM categories

WHERE id = ?

UNION ALL

SELECT categories.id, categories.name, categories.parent_id, categories.lft, categories.rgt, categories.depth

FROM categories, cte

WHERE categories.parent_id = cte.id

)

SELECT * FROM cte ORDER BY depth;

其中，？表示根节点的id。

使用嵌套集合模型可以方便实现对树形结构的查询，但是在插入、删除和修改节点时需要重新计算整个子树的左右边界，较为复杂。

2. 路径模型

路径模型是一种简单的层次模型，它用一种字符串类型的路径表示父子关系，例如1/2/3表示节点3是节点2的子节点，节点2又是节点1的子节点。下面是一个示例表：

CREATE TABLE categories (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

parent_id INT,

path VARCHAR(255) NOT NULL,

UNIQUE KEY(name)

);

在插入数据时，需要通过递归方式计算出path的值：

INSERT INTO categories (name, parent_id, path)

VALUES ( Music , NULL, 1/ );

INSERT INTO categories (name, parent_id, path)

VALUES ( Pop , 1, 1/2/ );

INSERT INTO categories (name, parent_id, path)

VALUES ( Rock , 1, 1/3/ );

INSERT INTO categories (name, parent_id, path)

VALUES ( Classic , 2, 1/2/4/ );

INSERT INTO categories (name, parent_id, path)

VALUES ( Metal , 4, 1/2/4/5/ );

INSERT INTO categories (name, parent_id, path)

VALUES ( Indie , 3, 1/3/6/ );

查询某个节点的子节点可以使用如下查询语句：

SELECT * FROM categories WHERE parent_id = ?;

递归查询可以使用如下查询语句：

SELECT * FROM categories WHERE path LIKE 1/2/%

其中，%表示任意长度的后续路径。

路径模型简单易用，但是在查询某个节点的所有祖先节点时需要使用LIKE语句进行模糊查询，效率较低。

3. 材料化路径模型

材料化路径模型是一种在路径模型的基础上进一步优化的方法，它在表中额外保存了节点的所有祖先节点的path信息，以便更加高效地进行查询。下面是一个示例表：

CREATE TABLE categories (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

parent_id INT,

path VARCHAR(255) NOT NULL,

ancestors TEXT,

UNIQUE KEY(name)

);

在插入数据时，需要通过递归方式计算出path和ancestors的值：

INSERT INTO categories (name, parent_id, path, ancestors)

VALUES ( Music , NULL, 1/ , );

INSERT INTO categories (name, parent_id, path, ancestors)

VALUES ( Pop , 1, 1/2/ , 1/ );

INSERT INTO categories (name, parent_id, path, ancestors)

VALUES ( Rock , 1, 1/3/ , 1/ );

INSERT INTO categories (name, parent_id, path, ancestors)

VALUES ( Classic , 2, 1/2/4/ , 1/2/ );

INSERT INTO categories (name, parent_id, path, ancestors)

VALUES ( Metal , 4, 1/2/4/5/ , 1/2/4/ );

INSERT INTO categories (name, parent_id, path, ancestors)

VALUES ( Indie , 3, 1/3/6/ , 1/3/ );

查询某个节点的子节点可以使用如下查询语句：

SELECT * FROM categories WHERE parent_id = ?;

递归查询可以使用如下查询语句：

WITH RECURSIVE cte AS (

SELECT id, name, parent_id, path, ancestors

FROM categories

WHERE id = ?

UNION ALL

SELECT categories.id, categories.name, categories.parent_id, categories.path, categories.ancestors

FROM categories, cte

WHERE categories.id IN (SELECT SUBSTRING_INDEX(ancestors, / , cte.depth + 1) FROM categories WHERE id = ?)

)

SELECT * FROM cte ORDER BY LENGTH(path);

其中，？表示根节点的id。

使用材料化路径模型可以方便实现对树形结构的查询，并且相较于路径模型，查询某个节点的所有祖先节点的效率更高。但是在插入、删除和修改节点时需要重新计算所有子孙节点的ancestors值，较为复杂。

综上所述，使用嵌套集合模型、路径模型或材料化路径模型都可以实现上下级关联，使用哪种方式取决于具体的应用场景，需要根据系统的需求进行选择。

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 MySQL实现上下级关联的方法简介（MySQL上下关联）