MySQL 樹結構核心原理與操作全解析：嵌套集模型實戰

從一個真實踩坑故事說起

早期使用 CakePHP ACL 插件時遇到 acos 表含 lft/rght 字段，不知其意直接修改導致數據錯亂。後來才發現這正是**嵌套集模型**的左右值編碼。本文系統梳理原理、查詢、插入、維護全流程。

核心原理：後序遍歷編號

對樹進行後序遍歷（左子樹 → 右子樹 → 根），每次進入節點遞增計數器賦予 left_num，離開節點再次遞增賦予 right_num。任意節點的子孫滿足：child.left BETWEEN parent.left AND parent.right。

樣本數據構建

DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
  `comment_id` INT(11) DEFAULT NULL,
  `left_num`   INT(11) DEFAULT NULL,
  `right_num`  INT(11) DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO `comment` VALUES 
(1,1,14), (2,2,5), (3,3,4), (4,6,13), (5,7,8), (6,9,12), (7,10,11);

CREATE INDEX idx_lr ON `comment` (`left_num`, `right_num`);

四大核心查詢模式

1. 獲取節點 4 的所有子孫（包含自身）

SELECT c.* 
FROM comment AS p, comment AS c 
WHERE c.left_num BETWEEN p.left_num AND p.right_num 
  AND p.comment_id = 4;

2. 獲取節點 6 的所有祖先（包含自身）

SELECT p.* 
FROM comment AS p, comment AS c 
WHERE c.left_num BETWEEN p.left_num AND p.right_num 
  AND c.comment_id = 6;

3. 計算節點 4 的深度

-- MySQL 5.7+ 需調整 sql_mode 允許非聚合列
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

SELECT c.*, COUNT(c.comment_id) AS depth
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
  AND c.comment_id = 4
GROUP BY c.comment_id;

4. 獲取節點 4 所有子孫及其相對深度

SELECT sub_child.*, (COUNT(sub_parent.comment_id) - 1) AS depth
FROM (
  SELECT child.* FROM comment AS parent, comment AS child
  WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
    AND parent.comment_id = 4
) AS sub_child,
(
  SELECT child.* FROM comment AS parent, comment AS child
  WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
    AND parent.comment_id = 4
) AS sub_parent
WHERE sub_child.left_num BETWEEN sub_parent.left_num AND sub_parent.right_num
GROUP BY sub_child.comment_id
ORDER BY sub_child.left_num;

插入新節點：為節點 4 添加子節點 44（置於最左側）

更新步驟

-- 1. 獲取父節點 4 的左值
SELECT left_num FROM comment WHERE comment_id = 4;  -- 6

-- 2. 所有左值 > 6 的 +2，右值 > 6 的 +2
UPDATE comment SET left_num  = left_num  + 2 WHERE left_num  > 6;
UPDATE comment SET right_num = right_num + 2 WHERE right_num > 6;

-- 3. 插入新節點：左值=父左值+1=7，右值=父左值+2=8
INSERT INTO comment SELECT 44, left_num + 1, left_num + 2 FROM comment WHERE comment_id = 4;

驗證

-- 子孫查詢
SELECT c.* FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num AND p.comment_id = 4;
-- 祖先查詢
SELECT p.* FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num AND c.comment_id = 44;

適用場景與避坑指南

生產環境維護建議

1. 大批量結構變更離線重建：單條更新鎖表風險大，建議導出 → 程式重算左右值 → 截斷表 → 批量導入。
2. 冗餘 pid 字段：兼容舊邏輯、便於單層 CRUD、支持 8.0 遞歸 CTE 回溯。
3. 監控區間完整性：定期跑 SELECT COUNT(*) FROM t WHERE left_num >= right_num 檢測異常。
4. 考慮將樹結構拆分表：核心業務表僅存 id, pid，樹屬性表存 id, left_num, right_num, depth，降低主表寫入鎖競爭。

總結

嵌套集模型以空間換時間，將樹查詢轉化為索引範圍掃描，**查詢性能極強、寫入維護成本可控**。掌握「左值右值區間包含」這一核心判斷邏輯，即可靈活應對各類多層級業務查詢需求。