
CREATE TABLE category (
category_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
parent_id INT DEFAULT 0,
KEY idx_parent (parent_id)
);
SELECT t1.name AS lev1, t2.name AS lev2, t3.name AS lev3, t4.name AS lev4
FROM category t1
LEFT JOIN category t2 ON t2.parent_id = t1.category_id
LEFT JOIN category t3 ON t3.parent_id = t2.category_id
LEFT JOIN category t4 ON t4.parent_id = t3.category_id
WHERE t1.name = 'ELECTRONICS';
缺點:層級不固定時需遞歸查詢或應用層遞歸,深度大時效能差;查詢「某頂級分類下所有商品」需多次 JOIN 或 UNION。


CREATE TABLE category (
category_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL,
parent_id INT DEFAULT 0, -- 保留父節點便於向上遍歷
KEY idx_lft_rgt (lft, rgt)
);

lft=1, rgt=2*N(N 為總節點數)。rgt = lft + 1。-- 取得節點 5 的所有子孫(含自身)
SELECT * FROM category WHERE lft > 5 AND rgt < 20;
-- 取得節點 5 的所有祖先
SELECT * FROM category WHERE lft < 5 AND rgt > 20 ORDER BY lft;
-- 統計節點 5 直系子節點數
SELECT COUNT(*) FROM category WHERE parent_id = 5;
| 維度 | 遞歸遞歸 | 左右值 |
|---|---|---|
| 結構簡單度 | ⭐⭐⭐⭐⭐ | ⭐⭐⭐ |
| 查詢子樹效能 | ⭐⭐(深度大時差) | ⭐⭐⭐⭐⭐(單次範圍查詢) |
| 插入/移動節點 | ⭐⭐⭐⭐⭐(只改 parent_id) | ⭐(需重算所有後續節點 lft/rgt) |
| 刪除節點 | ⭐⭐⭐⭐ | ⭐(同插入,需重算) |
| 適用場景 | 分類樹較穩定、深度不固定 | 讀多寫少、固定深度、需高效子樹查詢 |
path 欄位(物化路徑 1/5/12/)支援 LIKE '1/5/%' 查詢。WITH RECURSIVE CTE,遞歸遞歸查詢單條 SQL 解決,無需左右值。