無限分類(多級分類)是電商、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>';
?>
額外建立 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 的所有子孫
<!-- 遞迴組件 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 Table | 1 | 高 | 極快 | 大規模、頻繁移動、複雜查詢 |
| 物化路徑 | 1 | 低 | 需更新路徑 | 路徑如 001/005/012/,LIKE 查詢快 |