您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 多層級結構實戰：區域表嵌套集模型完整指南

編輯：MySQL 資料庫相關知識

背景與模型選型

處理「省-市-區」、「分類樹」、「組織架構」等多層級數據，常見三種模型：

模型	查詢子孫	查詢祖先	寫入性能	適用場景
鄰接表	遞歸/多次查詢	遞歸/多次查詢	極快	寫多讀少、層級淺
路徑枚舉	LIKE 前綴匹配	LIKE 後綴匹配	中等	路徑固定、需全路徑展示
嵌套集	範圍查詢 O(1)	範圍查詢 O(1)	較慢(需更新區間)	讀多寫少、層級深、頻繁查詢子樹

本文採用 嵌套集模型，字段 left_num、right_num 代表節點在後序遍歷中的左右邊界值。

表結構定義

CREATE TABLE `area` (
  `area_id`   INT(11)      NOT NULL AUTO_INCREMENT COMMENT '地區ID',
  `name`      VARCHAR(40)  NOT NULL DEFAULT 'unknown' COMMENT '地區名稱',
  `area_code` VARCHAR(10)  NOT NULL DEFAULT 'unknown' COMMENT '地區編碼',
  `pid`       INT(11)      DEFAULT NULL COMMENT '父ID(保留兼容)',
  `left_num`  MEDIUMINT(8) UNSIGNED NOT NULL COMMENT '節點左值',
  `right_num` MEDIUMINT(8) UNSIGNED NOT NULL COMMENT '節點右值',
  PRIMARY KEY (`area_id`),
  KEY `idx_pid` (`pid`),
  KEY `idx_left`  (`left_num`),
  KEY `idx_right` (`right_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心查詢模板（以「廣州市 area_id=2148」為例）

1. 查詢節點自身

SELECT * FROM area WHERE area_id = 2148;

2. 查詢所有子孫節點（包含自身）

SELECT c.* 
FROM area AS p, area AS c 
WHERE c.left_num BETWEEN p.left_num AND p.right_num 
  AND p.area_id = 2148;

3. 查詢子孫節點並顯示層級深度

SELECT 
  sub_child.area_id,
  (COUNT(sub_parent.name) - 1) AS depth,
  CONCAT(REPEAT('  ', COUNT(sub_parent.name) - 1), sub_child.name) AS name
FROM (
  SELECT child.* FROM area AS parent, area AS child
  WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
    AND parent.area_id = 2148
) AS sub_child,
(
  SELECT child.* FROM area AS parent, area AS child
  WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
    AND parent.area_id = 2148
) AS sub_parent
WHERE sub_child.left_num BETWEEN sub_parent.left_num AND sub_parent.right_num
GROUP BY sub_child.area_id
ORDER BY sub_child.left_num;

輸出示例：depth=0 廣州市、depth=1 天河區、depth=1 海珠區...

4. 查詢所有祖先節點（包含自身）

SELECT p.* 
FROM area AS p, area AS c 
WHERE c.left_num BETWEEN p.left_num AND p.right_num 
  AND c.area_id = 2148;

輸出：中國 → 廣東省 → 廣州市

寫入操作：插入新節點「南沙區」作為廣州子節點

核心原理：新節點插入父節點左值右側，所有受影響節點的左右值 +2。

-- 1. 獲取父節點左值
SELECT left_num FROM area WHERE area_id = 2148;  -- 假設為 2879

-- 2. 更新區間：所有左值 > 2879 的 +2，右值 > 2879 的 +2
UPDATE area SET left_num  = left_num  + 2 WHERE left_num  > 2879;
UPDATE area SET right_num = right_num + 2 WHERE right_num > 2879;

-- 3. 插入新節點：左值 = 父左值+1，右值 = 父左值+2
INSERT INTO area (name, area_code, pid, left_num, right_num)
SELECT '南沙區', '440115', 2148, left_num + 1, left_num + 2
FROM area WHERE area_id = 2148;

驗證插入結果

SELECT c.* FROM area AS p, area AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
  AND p.area_id = 2148
ORDER BY c.left_num;

新節點 南沙區 正確出現在廣州直屬子節點首位。

性能與維護建議

讀多寫少場景首選：區域表、分類表通常日寫入 < 10 次，日查詢 > 10 萬次，嵌套集極優。
批量重建優於單條更新：大規模結構變更（如行政區劃調整）建議離線重算左右值，再整表替換。
輔助 pid 字段保留：兼容舊代碼、便於單層級 CRUD、支持遞歸 CTE（MySQL 8.0+）。
索引策略：left_num、right_num 單列索引滿足範圍查詢；複合索引 (left_num, right_num) 可覆蓋「查詢子孫+排序」場景。