Materialized Path 树形结构

text 中国 path: /1/ ├── 浙江省 path: /1/1/ │ ├── 杭州市 path: /1/1/1/ │ └── 宁波市 path: /1/1/2/ └── 江苏省 path: /1/2/ └── 南京市 path: /1/2/1/

每个节点的路径由**祖先节点ID + 分隔符**组成，形成一条完整的血缘链。

### 表结构设计

最少需要三个字段：

```sql
CREATE TABLE categories (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    path  VARCHAR(1000) NOT NULL,
    depth INT NOT NULL DEFAULT 1
);

• path：节点的完整祖先路径，通常包含自身ID，如 /1/3/5/。
• depth：节点所处的层级（根为1），辅助快速计算和排序。

分隔符建议：使用 /、. 或 ,，但路径两端必须包含分隔符，例如 /1/3/，方便使用 LIKE 查询时避免误匹配（防止 LIKE '/1/2%' 匹配到 /1/20/）。

核心操作详解

假设分隔符为 /，路径两端带 /，各级 ID 为数字。

1. 插入节点

插入之前需知道父节点的 path 和 depth。

例：向 ID 为 5 的父节点下插入新节点

-- 先查出父信息
SELECT path, depth FROM categories WHERE id = 5;
-- 假设结果为 path = '/1/3/5/', depth = 3

-- 获取新节点ID（假设为自增），插入后拿到新ID = 8
INSERT INTO categories (name, path, depth) 
VALUES ('新分类', CONCAT('/1/3/5/', '8', '/'), 4);

注意：实际中可以在事务内先插入得到ID，再更新路径，或者使用预计算序列ID的方式。

2. 查询所有子节点（子树）

利用 path LIKE 前缀查询，性能极高，尤其适合深度不定但需要把整棵子树拉出的场景。

-- 查询 ID=5 的所有子孙节点（包含自身？按需决定）
SELECT * FROM categories 
WHERE path LIKE '/1/3/5/%'   -- 子孙
   OR path = '/1/3/5/';      -- 自身（如果需要）

LIKE '/1/3/5/%' 能利用前缀索引 path (varchar_pattern_ops) 加速。

3. 查询直接子节点

添加深度条件：

-- 父节点depth为3，则直接子节点depth = 4
SELECT * FROM categories 
WHERE path LIKE '/1/3/5/%' 
  AND depth = 4;

4. 查询祖先链（面包屑导航）

物化路径天然包含祖先信息，无需递归查询。可以按分隔符拆分 path 后得到各级ID。

方案一：在应用层处理 把路径 /1/3/5/ 去掉首尾分隔符得到 1,3,5，然后挨个查询。

方案二：纯SQL实现（利用IN）

SELECT * FROM categories 
WHERE id IN (1, 3, 5)
ORDER BY depth ASC;

拆分路径的SQL技巧（MySQL示例）：

-- 使用SUBSTRING_INDEX提取各级ID（适用于层数固定或较少的情况）
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', 2), '/', -1) AS level1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', 3), '/', -1) AS level2
...

5. 移动子树（变更父节点）

移动操作是物化路径的短板，因为一个节点的路径变化会导致所有后代的路径需要更新。

假设将节点5（原本path='/1/3/5/'）移动到节点2（path='/1/2/'）下

-- 新父节点信息
SELECT path FROM categories WHERE id = 2; -- 得到 '/1/2/'

-- 节点5新路径 = '/1/2/5/'
-- 需要同时更新节点5及其所有子孙的路径，替换旧前缀
UPDATE categories 
SET path = REPLACE(path, '/1/3/5/', '/1/2/5/')
WHERE path LIKE '/1/3/5/%' OR path = '/1/3/5/';

若同时维护 depth，需要一并修正：

-- 新depth变化量 = 新父depth + 1 - 原depth
-- 假设原depth=3，新父depth=2，则增量 = 3 - 1 = -1? 注意计算：新层级 = 2+1 = 3，所以depth不变？确切需要动态计算。
-- 简洁做法：先更新path，再统一根据path计算depth
UPDATE categories c
JOIN (SELECT id, LENGTH(path) - LENGTH(REPLACE(path, '/', '')) - 1 AS new_depth FROM categories) t
SET c.depth = t.new_depth
WHERE c.path LIKE '/1/2/5/%' OR c.path = '/1/2/5/';

移动大量节点时注意事务和性能。

6. 删除子树

与查询子树逻辑一致，使用 path LIKE 条件删除即可。

DELETE FROM categories 
WHERE path LIKE '/1/3/5/%' OR path = '/1/3/5/';

搭配软删除时同理。

物化路径的优缺点

优点

• 查询子树极其简单高效：一个 LIKE 即可获取所有后代，无需递归。
• 查询祖先方便：路径中直接包含所有祖先ID，拆分后批量查询。
• 结构清晰，便于调试：直接查看 path 就能理解层级关系。
• 适合深度不定、频繁读取子树的场景，如分类列表、文件目录。

缺点

• 移动/重排序代价高：移动节点需要更新该节点及所有后代路径字符串，涉及大量行更新。
• 路径长度限制：深度过大或ID较长时，VARCHAR 字段需要预留足够空间。
• 难以维护完整性：如果更新路径时出错，容易造成数据不一致，需要额外约束或程序保证。
• 不直接支持 “同级排序”：若需按指定顺序返回，通常需增加 order 字段配合。

与其他树形方案的对比

适用场景：分类层级不常变动，但需要频繁查询子树或祖先的场景。例如电商类目、静态目录系统、区域规划等。

实践建议与注意事项

1. 路径分隔符：始终让路径以分隔符开头和结尾，避免 LIKE 误匹配。
2. 索引：为 path 字段建立前缀索引 (如果数据库支持 varchar_pattern_ops 或前缀索引)，提升 LIKE '/xxx/%' 查询速度。
3. 深度列：冗余存储 depth，方便按层级查询和排序，减少计算。
4. ID长度对策：如果ID可能较大（如UUID），考虑使用进制压缩或使用定长编码，控制路径总长度。
5. 移动操作：将移动放在事务中，并在应用层进行双重校验。频繁变动的树不建议使用物化路径。
6. 与 Closure Table 结合：有时会混用，将 ancestor, descendant, depth 表用于复杂查询，而物化路径仅作辅助。

一个完整的MySQL示例

-- 建表
CREATE TABLE regions (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    path  VARCHAR(500) NOT NULL DEFAULT '',
    depth TINYINT UNSIGNED NOT NULL
);

-- 插入根节点
INSERT INTO regions (name, path, depth) VALUES ('全球', '/1/', 1);

-- 插入子节点 (假设已获得父ID=1)
INSERT INTO regions (name, path, depth) VALUES 
('亚洲', '/1/2/', 2),
('欧洲', '/1/3/', 2);

-- 再插入亚洲的子节点 (父path='/1/2/', depth=2, 新ID=4)
INSERT INTO regions (name, path, depth) VALUES ('中国', '/1/2/4/', 3);

-- 查询亚洲及其所有后代
SELECT * FROM regions WHERE path LIKE '/1/2/%' OR path = '/1/2/';

-- 查询中国的祖先
SELECT * FROM regions WHERE id IN (1, 2) ORDER BY depth;