萬盛學電腦網

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

PHP + MySQL 無限分類實作:遞歸與樹形類別完整方案

前言

無限分類(多級分類)是電商、CMS 核心模型。本文介紹兩種經典實作:遞歸查詢法(適合數據量小、即時性要求高)與 樹形類別法(一次查詢、記憶體組裝、高效能),並提供完整代碼。

資料表設計

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0 COMMENT '父分類ID,0為頂級',
  `name` varchar(50) NOT NULL COMMENT '分類名稱',
  `sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序',
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

方案一:遞歸查詬(簡單直觀)

<?php
function getChildren($parentId = 0, $level = 0) {
    global $pdo;
    $stmt = $pdo->prepare('SELECT * FROM category WHERE parent_id = ? ORDER BY sort, id');
    $stmt->execute([$parentId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $row) {
        echo str_repeat(' ', $level * 4) . "{$row['name']}<br/>";
        getChildren($row['id'], $level + 1); // 遞歸查子分類
    }
}

// 使用
getChildren(0); // 從頂級開始
?>

缺點:N+1 查詢問題,分類多時查詢次數爆炸。

方案二:一次查詢 + 記憶體組裝樹形類(高效能)

<?php
class CategoryTree {
    private $data = [];   // 所有分類數據 [id => row]
    private $tree = [];   // 組裝後的樹
    private $icons = ['│', '├', '└'];

    public function setData(array $rows) {
        foreach ($rows as $row) {
            $this->data[$row['id']] = $row;
        }
    }

    public function getTree($rootId = 0, $prefix = '', $isLast = true) {
        $children = $this->getChildren($rootId);
        if (!$children) return;

        $count = count($children);
        foreach ($children as $i => $child) {
            $isLastChild = ($i === $count - 1);
            $icon = $isLastChild ? $this->icons[2] : $this->icons[1];
            $connector = ($prefix === '') ? '' : ($isLast ? '  ' : $this->icons[0] . ' ');

            $this->tree[] = [
                'id' => $child['id'],
                'name' => $prefix . $connector . $icon . ' ' . $child['name'],
                'level' => $this->getLevel($child['id']),
                'parent_id' => $child['parent_id']
            ];

            $newPrefix = $prefix . $connector . ($isLastChild ? '  ' : $this->icons[0] . ' ');
            $this->getTree($child['id'], $newPrefix, $isLastChild);
        }
    }

    private function getChildren($parentId) {
        $result = [];
        foreach ($this->data as $row) {
            if ($row['parent_id'] == $parentId) $result[] = $row;
        }
        return $result ? $result : false;
    }

    private function getLevel($id) {
        $level = 0;
        while (($pid = $this->data[$id]['parent_id'] ?? 0) > 0) {
            $level++;
            $id = $pid;
        }
        return $level;
    }

    public function getResult() {
        return $this->tree;
    }
}

// 使用範例
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '');
$rows = $pdo->query('SELECT * FROM category ORDER BY parent_id, sort')->fetchAll(PDO::FETCH_ASSOC);

$tree = new CategoryTree();
$tree->setData($rows);
$tree->getTree(0);
$categories = $tree->getResult();

// 輸出下拉選單
echo '<select name="category_id">';
foreach ($categories as $cat) {
    echo '<option value="' . $cat['id'] . '">' . htmlspecialchars($cat['name']) . '</option>';
}
echo '</select>';
?>

方案三:Closure Table(閉包表) - 適合極大規模/頻繁移動

額外建立 category_closure 表記錄所有祖先後代關係:

CREATE TABLE category_closure (
    ancestor INT NOT NULL,
    descendant INT NOT NULL,
    depth INT NOT NULL,
    PRIMARY KEY (ancestor, descendant)
);

-- 移動子樹只需操作 closure 表,查詢後代/祖先極快
SELECT c.* FROM category c
JOIN category_closure cc ON c.id = cc.descendant
WHERE cc.ancestor = 5 AND cc.depth > 0; -- 獲取 ID=5 的所有子孫

前端無限級選單渲染(Vue/React 通用)

<!-- 遞迴組件 TreeSelect.vue -->
<template>
  <ul>
    <li v-for="node in tree" :key="node.id">
      <label><input type="checkbox" :value="node.id"> {{ node.name }}</label>
      <TreeSelect v-if="node.children" :tree="node.children" />
    </li>
  </ul>
</template>

效能對比

方案查詢次數記憶體移動子樹適用場景
遞歸查詢N+1低簡單分類<1000、低頻變更
樹形類別1中需重組分類<10000、高頻讀取
Closure Table1高極快大規模、頻繁移動、複雜查詢
物化路徑1低需更新路徑路徑如 001/005/012/,LIKE 查詢快
copyright © 萬盛學電腦網 all rights reserved