PHP+MySQL 實現無限級分類（遞歸 + 單次查詢）

資料表設計

CREATE TABLE `category` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(50) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `sort_order` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`cat_id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心思路：一次查詢 + PHP 遞歸構建樹

function getCategoryTree($catList, $parentId = 0, $level = 0, $indent = '│─') {
    $tree = [];
    foreach ($catList as $cat) {
        if ($cat['parent_id'] == $parentId) {
            $cat['level'] = $level;
            $cat['indent'] = str_repeat($indent, $level);
            $tree[] = $cat;
            // 遞歸獲取子分類
            $tree = array_merge($tree, getCategoryTree($catList, $cat['cat_id'], $level + 1, $indent));
        }
    }
    return $tree;
}

// 使用
$sql = 'SELECT * FROM category ORDER BY sort_order ASC, cat_id ASC';
$list = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$tree = getCategoryTree($list);

// 輸出下拉選單範例
echo '<select name="cat_id">';
foreach ($tree as $c) {
    echo '<option value="'.$c['cat_id'].'">'.$c['indent'].$c['cat_name'].'</option>';
}
echo '</select>';

進階：構建多維陣列樹（適合 JSON API）

function buildTreeArray($list, $pid = 0) {
    $tree = [];
    foreach ($list as $item) {
        if ($item['parent_id'] == $pid) {
            $children = buildTreeArray($list, $item['cat_id']);
            if ($children) $item['children'] = $children;
            $tree[] = $item;
        }
    }
    return $tree;
}
$tree = buildTreeArray($list);
echo json_encode($tree, JSON_UNESCAPED_UNICODE);

效能優化建議

前端無限級渲染（Vue 3 範例）

<script setup>
const props = defineProps(['tree']);
</script>
<template>
  <ul>
    <li v-for="node in tree" :key="node.cat_id">
      {{ node.indent }}{{ node.cat_name }}
      <Tree :tree="node.children" v-if="node.children" />
    </li>
  </ul>
</template>

總結

「一次查詢 + 遞歸組裝」是 PHP 實現無限級分類最經典、維護成本最低的方案。配合 level/indent 輸出縮進，前端直接綁定即可。大數據場景請加緩存或改用 Closure Table。