AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL实现嵌套集合模型

2016-05-20 3547
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

译文主要是介绍如何用MySQL来存储嵌套集合数据。在其中会增加一些自己的理解，也会删除掉一些自认为无用的废话。
这篇文章主要讲的是嵌套集合模型，所以邻接表不是本文的重点，简单略过就好。

也许这是原文地址，因为我也不知道这是不是原文。

介绍

什么是分层数据？

类似于树形结构，除了根节点和叶子节点外，所有节点都有用一个父节点和多个子节点。

那么，在MySQL中如何处理分层数据呢？

原文中介绍了两种分层结构模型：邻接表模型和嵌套集合模型。

邻接表模型(The Adjacency List Model)

首先，建立测试表，导入测试数据，

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES
        (1,'ELECTRONICS',NULL),
        (2,'TELEVISIONS',1),
        (3,'TUBE',2),
        (4,'LCD',2),
        (5,'PLASMA',2),
        (6,'PORTABLE ELECTRONICS',1),
        (7,'MP3 PLAYERS',6),
        (8,'FLASH',7),
        (9,'CD PLAYERS',6),
        (10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

在邻接表中，所有的数据均拥有一个Parent字段，用来存储它的父节点。当前节点为根节点的话，它的父节点则为NULL。
那么在遍历的时候，可以使用递归来实现查询整棵树，从根节点开始，不断寻找子节点（父节点->子节点->父节点->子节点）。

检索分层路径

一般需要获取一个分层结构的路径问题，那么

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

检索叶子节点

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

检索指定路径

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)

邻接表的缺点

在检索路径的过程中，除了本层外，每一层都会对应一个LEFT JOIN，那么如果层数不定怎么办？或者层数过多？
在删除中间层的节点时，需要同时删除该节点下的所有节点，否则会出现孤立节点。

嵌套集合模型Nested Set Model

原文中主要的目的是介绍嵌套集合模型，如下

通过集合的包含关系，嵌套结合模型可以表示分层结构，每一个分层可以用一个Set来表示（一个圈），父节点所在的圈包含所有子节点所在的圈。

为了用MySQL来表示集合关系，需要定义连个字段left和right（表示一个集合的范围）。

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO nested_category VALUES
  (1,'ELECTRONICS',1,20),
  (2,'TELEVISIONS',2,9),
  (3,'TUBE',3,4),
  (4,'LCD',5,6),
  (5,'PLASMA',7,8),
  (6,'PORTABLE ELECTRONICS',10,19),
  (7,'MP3 PLAYERS',11,14),
  (8,'FLASH',12,13),
  (9,'CD PLAYERS',15,16),
  (10,'2 WAY RADIOS',17,18);

SELECT * FROM nested_category ORDER BY category_id;

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

由于left和right是MySQL的保留字，因此，字段名称用lft和rgt代替。每一个集合都是从lft开始到rgt结束，也就是集合的两个边界。

在树中也同样适用，

当为树状结构编号时，我们从左到右，一次一层，赋值按照从左到右的顺序遍历其子节点，这种方法称为先序遍历算法。

检索分层路径

由于子节点的lft值总在父节点的lft和rgt值之间，所以可以通过父节点连接到子节点上来检索整棵树。

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+</pre>

这个方法并不需要考虑层数，而且不需要考虑节点的rgt。

检索所有叶子节点

由于每一个叶子节点的rgt=lft+1，那么只需要这一个条件即可。

SELECT name
FROM nested_category
WHERE rgt = lft + 1;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

检索节点路径

不再需要多个join连接操作。

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

检索节点深度

通过COUNT和GROUP BY函数来获取父节点的个数。

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+

甚至可以得到分层的缩进结果，

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

检索子树的深度

考虑到检索中需要自连接的node或parent，因此需要增加一个额外的连接来作为子查询来限制子树。

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

检索节点的直接子节点

假设一个场景，当用户点击网站上电子产品的一个分类时，将呈现该分类下的产品，同时需要列出所有子分类，并不是全部分类。
为了限制显示分类的层数，需要使用HAVING字句，

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth &lt;= 1
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

增加新节点

上面已经介绍了如何检索结果，那么如何才能增加新的节点呢？

如果希望在TELEVISIONS和PROTABLE ELECTRONICS节点之间增加一个新的节点，那么新节点的lft和rgt的值应该是10和11，那么所有大于10的节点（新节点右侧的节点）的lft和rgt都应该加2，如上图所示。

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt &gt; @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft &gt; @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES

如果希望在叶子节点下增加节点，需要修改下查询语句，

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = '2 WAY RADIOS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt &gt; @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft &gt; @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;```



###删除节点

删除叶子节点比较容易，只需要删除自己，而删除一个中间层节点就需要删除其所有子节点。在这个模型中，所有子节点的节点正好在lft和rgt之间。

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;


在某些情况下，只需要删除某个节点，但是并不希望删除该节点下的子节点数据。
通过把右侧所有节点的左右值-2，当前节点的子节点左右值-1

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';

DELETE FROM nested_category WHERE lft = @myLeft;

UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

UNLOCK TABLES;
```

最后的思考

原作者推荐了一本名为《Joe Celko's Trees and Hierarchies in SQL for Smarties》的书籍，该书的作者是SQL领域的大神Joe Celko（嵌套几何模型的创造者）。这本书涵盖了本文中未涉及到的一些高级话题。

知识共享许可协议
本文 由 cococo点点 创作，采用 知识共享 署名-非商业性使用-相同方式共享 3.0 中国大陆 许可协议进行许可。欢迎转载，请注明出处：
转载自：cococo点点 http://www.cnblogs.com/coder2012


文章标签：
云数据库 RDS MySQL 版
内存技术
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版模型
云数据库 RDS MySQL 版嵌套
云数据库 RDS MySQL 版集合
云数据库 RDS MySQL 版实现
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
ghost丶桃子
目录
相关文章
技术蜜糖罐
|
SQL 数据可视化 关系型数据库
轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
技术蜜糖罐
1127 0
Python编程站
|
SQL 关系型数据库 MySQL
flask中生成迁移脚本并将orm模型映射至MySQL
flask中生成迁移脚本并将orm模型映射至MySQL
Python编程站
456 0
愿天堂没有BUG（公众号同名）
|
关系型数据库 MySQL 数据库
数据迁移脚本优化过程：从 MySQL 到 Django 模型表
在大规模的数据迁移过程中，性能问题往往是开发者面临的主要挑战之一。本文将分析一个数据迁移脚本的优化过程，展示如何从 MySQL 数据库迁移数据到 Django 模型表，并探讨优化前后的性能差异。
愿天堂没有BUG（公众号同名）
276 1
拉丁解牛说技术
|
10月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制，包括表空间、段、区、页和行的具体结构，以及如何设计和优化行数据存储以提高性能。
拉丁解牛说技术
279 1
老板这功能得加钱
|
算法 Java 数据库连接
Spring+MySQL+数据结构+集合，Alibaba珍藏版mybatis手写文档
Spring+MySQL+数据结构+集合，Alibaba珍藏版mybatis手写文档
老板这功能得加钱
108 1
nanshaws
|
存储 关系型数据库 MySQL
mysql的begin end嵌套
本文介绍了MySQL中如何使用`begin`和`end`关键字进行事务或存储过程的嵌套操作，并强调了编写嵌套代码时需要注意作用域的重要性。
nanshaws
234 0
mysql的begin end嵌套
阿甘兄
|
SQL 关系型数据库 MySQL
07 Django模型 - ORM简介及MySQL数据库的使用
07 Django模型 - ORM简介及MySQL数据库的使用
阿甘兄
173 0
做梦都在改BUG
|
关系型数据库 MySQL 数据库
『Django』模型入门教程-操作MySQL
一个后台如果没有数据库可以说废了一半。日常开发中大多数时候都在与数据库打交道。Django 为我们提供了一种更简单的操作数据库的方式。 在 Django 中，模型（Model）是用来定义数据库结构的类。每个模型类通常对应数据库中的一个表，类的属性对应表中的列。通过定义模型，Django 的 ORM（Object-Relational Mapping）可以将 Python 对象映射到数据库表，并提供一套 API 来进行数据库操作。 本文介绍模型的用法。
做梦都在改BUG
177 1
红目香薰
|
存储 关系型数据库 MySQL
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
红目香薰
628 1
aliyun3916161550-32116
|
SQL 关系型数据库 MySQL
简简单单 My SQL 学习笔记（3）——连接和嵌套查询
简简单单 My SQL 学习笔记（3）——连接和嵌套查询
aliyun3916161550-32116
164 4

热门文章

最新文章

  • 1
    PostgreSQL 最佳实践 - 逻辑增量复制(MySQL <-> PgSQL <-> PgSQL)
  • 2
    Docker部署WordPress LNMP(Nginx PHP MySQL)环境实践
  • 3
    提升数据安全与性能，掌握Helm一键部署MySQL 8.0主从技巧
  • 4
    Linux mysql 允许远程连接
  • 5
    MySQl 经典语句
  • 6
    datax从mysql导入数据到mysql
  • 7
    MySQL8的数据库备份导入MySQL5版本中
  • 8
    MySQL修改密码方法总结
  • 9
    Linux下MySQL数据库常用基本操作 一
  • 10
    关于java连接mysql数据库的几个问题的解决方法。
  • 1
    AI Agent越用越笨？阿里云AnalyticDB「AI上下文工程」一招破解！
    286
  • 2
    AI Agent越用越笨？阿里云AnalyticDB「AI上下文工程」一招破解！
    586
  • 3
    Apache Doris 3.1 正式发布：半结构化分析全面升级，湖仓一体能力再跃新高
    549
  • 4
    Flink基于Paimon的实时湖仓解决方案的演进
    427
  • 5
    一键搞定本土认证难题，AnalyticDB版Supabase助力AI应用实现支付宝&微信登录
    675
  • 6
    阿里云AnalyticDB for PostgreSQL 入选VLDB 2025：统一架构破局HTAP，Beam+Laser引擎赋能Data+AI融合新范式
    386
  • 7
    一文辨析：数据仓库、数据湖、湖仓一体
    620
  • 8
    一键搞定本土认证难题，AnalyticDB版Supabase助力AI应用实现支付宝&微信登录
    392
  • 9
    淘宝闪购基于Flink&Paimon的Lakehouse生产实践：从实时数仓到湖仓一体化的演进之路
    879
  • 10
    数据湖 vs 数据仓库：大厂为何总爱“湖仓并用”？
    1121

    • 相关课程

    更多
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    「直播预告」Streaming Lakehouse Meetup EP.2｜Paimon × StarRocks 共话实时湖仓