萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 無限分類與樹狀論壇實現:遞歸、路徑枚舉、閉包表三大模型深度解析

核心資料表結構 (鄰接表模型)

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;

模型一:遞歸查詢 (Adjacency List + Recursive CTE / PHP 遞歸)

PHP 遞歸獲取子樹 (適合中小數據量)

<?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);
?>

MySQL 8.0+ 遞歸 CTE (單查詢獲取完整層級路徑)

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);

模型對比與選型建議

維度遞歸/鄰接表路徑枚舉閉包表
查詢子樹遞歸查詢 / CTELIKE 前綴 (極快)JOIN closure (極快)
查詢祖先遞歸查詢LIKE 反向 (較慢)JOIN closure (極快)
移動子樹遞歸更新 parentidUPDATE 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 類隔離查詢邏輯,才能應對未來變化。

copyright © 萬盛學電腦網 all rights reserved