MySQL嵌套集模型（详解）

保持数据层次，父分类包围了其子分类。在数据表中，我们通过使用表示节点的嵌套关系的**左值(left value)和右值(right value)**来表现嵌套集合模型中数据的分层特性

邻接表是最常见的树形结构表示方式，通过记录每个节点的父节点实现树形结构。通常包含两个字段id和parent_id

• 邻接表与嵌套集结合：同时支持邻接表（parent_id）和嵌套集（left_node/right_node）两种模型，灵活应对不同场景：
  • 邻接表适合快速获取直接子节点。
  • 嵌套集适合高效查询整个子树。
• 优势是查询子树高效（无需递归），但插入/移动节点成本较高，需调整大量左右值

递归查询

在数据库中处理无限分类数据是一种常见的需求，尤其是在电子商务、内容管理系统等应用中，分层数据结构如组织架构、产品目录等至关重要

递归查询是通过一个初始查询和一个递归子查询来实现的。初始查询用于查找树的根节点，递归子查询则用于找到每个节点的子节点，并将它们连接起来。

初始查询SELECT id, parent_id, name FROM nodes WHERE parent_id IS NULL用于查找根节点。

递归部分SELECT n.id, n.parent_id, n.name FROM nodes n JOIN Tree t ON n.parent_id = t.id用于查找每个节点的子节点，并将它们加入结果集中。

优化

递归查询在处理大规模数据时可能会遇到性能瓶颈，因此需要进行优化。常见的优化方法包括：

• 限制递归深度：在递归查询中添加一个深度字段，并在查询条件中限制其最大深度。
• 索引优化：为表的id和parent_id字段添加索引，提高查询速度。
• 分段查询：将大规模数据分段处理，减少单次查询的负担。
• 左右值编码

左右值编码

在基于数据库的一般应用中，查询的需求总要大于删除和修改。为了避免对于树形结构查询时的“递归”过程，基于Tree的前序遍历设计一种全新的无递归查询、无限分组的左右值编码方案，来保存该树的数据。

• left_node: 每个节点都有一个唯一的左值，表示该节点在其所有子节点中的相对位置。
• right_node: 同理每个节点也有一个唯一的右值

相信大部分人都不清楚左值（Lft）和右值（Rgt）是如何计算出来的，而且这种表设计似乎并没有保存父子节点的继承关系。
但当你用手指指着表中的数字从1数到18，你应该会发现你手指移动的顺序就是对这棵树进行先序遍历的顺序，如上图所示

根据此设计，可以推断出所有左值>2，且右值<11的节点都是Fruit的子节点，整棵树的结构通过左值和右值存储了下来。

但这还不够，我们目的是能够对树进行CRUD操作，即需要构造出与之配套的相关算法。按照深度优先，由左到右的原则遍历整个树，从1开始给每个节点标注上left值和right值，并将这两个值存入对应的name之中。

但当插入或删除节点时，可能需要更新大量相关的 left_node 和 right_node 值以保持树的一致性。这可能是嵌套集模型的一个缺点，尤其是在处理大型树时。

查询算法

查某个结点的所有子结点， 以Fruit为例，查询时，满足

Lft <= 2 AND Rgt >=11 ORDER BY Lft ASC

子孙总数 = (右值–左值–1)/2，以Fruit为例，其子孙总数为：(11–2–1)/2 = 4

获取节点在树中所处的层数，以Fruit为例：

select count(*) ... Lft <= 2 AND Rgt >=11   #即向上找

获取当前节点父节点，以Fruit为例

select * ... Lft <= 2 AND Rgt >=11 AND Layer=1   #layer是Fruit的父节点层级

获取当前节点所在路径，以Beaf为例：

select * ... Lft <= 13 AND Rgt >=14 ORDER BY Lft ASC  #即向上找

获取所有直属子节点，以Fruit为例：

select * ... Lft BETWEEN 2 AND 11 AND Layer=3  #layer是直属子节点层级

获取所有兄弟节点，以Fruit为例：

select * ... WHERE Rgt > 11 
AND Rgt < (SELECT Rgt FROM treeview WHERE Lft <= 2 AND Rgt >=11 AND Layer=1)  #查Fruit的父节点的 右边值
AND Layer=2   #本层

查询所有叶子节点，以Fruit为例：

 SELECT * ... WHERE Rgt = Lft + 1 AND Lft BETWEEN 2 AND 11 

插入算法

最重要是有一个根节点，为了便于控制查询级别，在建表的时候建议添加parent_id配合联结列表方式一起使用

CREATE TABLE IF NOT EXISTS `Tree` (
  `node_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL DEFAULT "0",
  `name` varchar(255) NOT NULL,
  `lft` int(11) NOT NULL DEFAULT "0",
  `rgt` int(11) NOT NULL DEFAULT "0",
  PRIMARY KEY (`node_id`),
  KEY `idx_left_right` (`lft`,`rgt`)
) DEFAULT CHARSET=utf8;

添加叶子结点

前插 初始化 根节点 1 2，每次前插构建时，按照规则进行构建即可

在Food下添加子节点Fruit为例：

可以发现，前插只需要 将所有>=Fruit左值的节点左右值+2

本身结点=（父节点左编码+1，左编码+2）

后插

后插规则这边和前插效果一样，只是构建的时候从后往前构建

以在Yellow后面添加Green为例

可以发现，后插兄弟结点，只需要 将所有>=Fruit右值的节点右值+2，所有左值>Fruit右值的节点左值+2

之后 本身结点=（父节点右编码-2，右编码-1）

添加非叶子结点

这个过程可以将流程分为几步，首先根据目标父节点Fruit，计算出(Right-Left)-1= 7-2-1=4 (Right-Left)+1= 7-2+1=6

1. 将所有>=Fruit右值的节点右值+(Right-Left)-1，所有左值 >=Fruit右值的节点右值+(Right-Left)-1，
2. 将Yellow-Banana 的左右值 都加上(Right-Left)+1，即+6
3. 更新Yellow的父节点

移动结点

移动节点其实就是更新对应层级，把结点及其子树移到其他层的结点下，主要涉及两块

源结点的父节点，要移到的目标父结点，这块就是前端指定的，可以获取到这块信息

1. 根据源节点id获取到其信息，和其父节点信息，比如上图的Beaf和Meat信息
2. 装配源节点信息到NodeCmd中，包括其层级，父节点左右值等
3. 判断如果源节点左值>源父结点左值，则前移，否则后移
   • 前移调用updateFormer，传入NodeCmd
   • 右移调用updateAfter，传入NodeCmd

前移updateFormer

首先updateFormerByLeft和updateFormerByRight

• 这两个语句是在为移动子树"腾出空间"
• 它们会把目标位置左侧的节点整体向右移动，空出一个"坑位"
• 比如要把子树A移动到B的左边，就先让B左边的节点都让开

然后updateFormer：

• 这个语句是实际执行移动操作
• 它会把子树从原来的位置"挖出来"
• 然后放到之前腾出的"坑位"里
• 同时会保持子树内部的结构不变，调整左右节点值

总结

这块如果问到，就说网上有相关代码，当时看了很久的，然后一遍遍迭代出sql代码，测试才过的，这块主要的难点就是

说比如要把某个分类下的类型移到另外一层分类，改变层级，就需要调整涉及到的相关树结点的左右值，保持平衡