CREATE TABLE `mf_sort` (
`sortid` SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`main` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, -- 根分類標識
`parentid` SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, -- 父分類 ID,0 為頂層
`layer` SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, -- 深度層級 (0 起)
`orders` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, -- 同層排序
`sort` VARCHAR(100) NOT NULL DEFAULT '', -- 分類名稱
PRIMARY KEY (`sortid`),
INDEX `idx_tree` (`main`, `parentid`, `layer`, `orders`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
<?php
function getChildren(PDO $pdo, int $parentId, int $main = 0): array
{
$stmt = $pdo->prepare('SELECT * FROM mf_sort WHERE parentid = ? AND main = ? ORDER BY orders, sortid');
$stmt->execute([$parentId, $main]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as &$row) {
$row['children'] = getChildren($pdo, $row['sortid'], $main);
}
return $rows;
}
// 獲取完整樹
$tree = getChildren($pdo, 0);
?>
WITH RECURSIVE category_tree AS (
-- 錨點:頂層分類
SELECT sortid, parentid, sort, layer, orders,
CAST(sort AS CHAR(1000)) AS path,
CAST(sortid AS CHAR(1000)) AS id_path
FROM mf_sort
WHERE parentid = 0 AND main = 1
UNION ALL
-- 遞歸部分:子分類
SELECT c.sortid, c.parentid, c.sort, c.layer, c.orders,
CONCAT(ct.path, ' > ', c.sort),
CONCAT(ct.id_path, ',', c.sortid)
FROM mf_sort c
INNER JOIN category_tree ct ON c.parentid = ct.sortid
WHERE c.main = 1
)
SELECT * FROM category_tree ORDER BY id_path;
在每條記錄儲存從根到當前節點的完整路徑 (如 1/5/12/)。
ALTER TABLE mf_sort ADD COLUMN `path` VARCHAR(500) NOT NULL DEFAULT '' AFTER `parentid`; -- 如 '1/5/12/'
-- 查詢某節點所有子孫 (極快,索引可用)
SELECT * FROM mf_sort WHERE path LIKE '1/5/12/%' AND main = 1;
-- 查詢某節點所有祖先
SELECT * FROM mf_sort WHERE '1/5/12/' LIKE CONCAT(path, '%') AND main = 1;
-- 移動子樹:更新 path 前綴
UPDATE mf_sort SET path = REPLACE(path, '1/5/12/', '1/8/20/') WHERE path LIKE '1/5/12/%';
優點:查詢子樹/祖先極快 (索引前綴匹配)。缺點:移動子樹需更新所有後代行;路徑長度有限制。
額外建立關係表,儲存所有祖先-後代關係 (含自身)。
CREATE TABLE `mf_sort_closure` (
`ancestor` SMALLINT(3) UNSIGNED NOT NULL,
`descendant` SMALLINT(3) UNSIGNED NOT NULL,
`depth` TINYINT(2) UNSIGNED NOT NULL, -- 0=自身, 1=子, 2=孫...
PRIMARY KEY (`ancestor`, `descendant`),
KEY `idx_descendant` (`descendant`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 觸發器/應用層維護:新增節點時
INSERT INTO mf_sort_closure (ancestor, descendant, depth)
SELECT ancestor, NEW.sortid, depth + 1 FROM mf_sort_closure WHERE descendant = NEW.parentid
UNION ALL SELECT NEW.sortid, NEW.sortid, 0;
-- 查詢節點 12 的所有子孫 (含自身)
SELECT c.* FROM mf_sort c
JOIN mf_sort_closure cl ON c.sortid = cl.descendant
WHERE cl.ancestor = 12 AND cl.depth > 0 ORDER BY cl.depth, c.orders;
-- 查詢節點 12 的所有祖先 (含自身)
SELECT c.* FROM mf_sort c
JOIN mf_sort_closure cl ON c.sortid = cl.ancestor
WHERE cl.descendant = 12 ORDER BY cl.depth;
-- 移動子樹:僅需更新 closure 表關係,無需遞歸更新所有後代行
-- 刪除子樹:DELETE FROM mf_sort_closure WHERE descendant IN (SELECT descendant FROM mf_sort_closure WHERE ancestor = 12);
| 維度 | 遞歸/鄰接表 | 路徑枚舉 | 閉包表 |
|---|---|---|---|
| 查詢子樹 | 遞歸查詢 / CTE | LIKE 前綴 (極快) | JOIN closure (極快) |
| 查詢祖先 | 遞歸查詢 | LIKE 反向 (較慢) | JOIN closure (極快) |
| 移動子樹 | 遞歸更新 parentid | UPDATE REPLACE path | 批次更新 closure (高效) |
| 插入節點 | 簡單 | 計算 path 簡單 | 需寫入多行 closure |
| 資料完整性 | FK 可強制 | 難強制 | FK 可強制 |
| 適用規模 | < 1 萬節點 | < 10 萬節點 | 任意規模 (推薦) |
| MySQL 版本需求 | 任意 (CTE 需 8.0+) | 任意 | 任意 |
<?php
function renderTree(array $tree, int $depth = 0): string
{
$html = '';
foreach ($tree as $node) {
$indent = str_repeat(' ', $depth);
$html .= "{$indent}📁 {$node['sort']}";
if (!empty($node['children'])) {
$html .= renderTree($node['children'], $depth + 1);
}
}
return $html;
}
echo renderTree($tree);
?>
無限分類是經典樹形結構問題。MySQL 8.0+ 專案首選 CTE 遞歸查詢 + 鄰接表,簡單標準;高頻讀取、大規模、頻繁移動子樹場景,**閉包表是唯一正解**;路徑枚舉適合中等規模、讀多寫少、不支援 CTE 的舊版本。根據業務讀寫比、樹深度、遷移成本選型,並封裝 Repository 類隔離查詢邏輯,才能應對未來變化。