樹形菜單數據庫設計：遞歸 CTE 與邻接表模型

經典三欄模型（鄰接表）

CREATE TABLE Menu (
    MenuId INT PRIMARY KEY,
    ParentId INT NULL REFERENCES Menu(MenuId), -- 根節點為 NULL
    Title NVARCHAR(100) NOT NULL,
    Url NVARCHAR(255),
    SortOrder INT DEFAULT 0,
    Icon NVARCHAR(50),
    IsVisible BIT DEFAULT 1
);

查詢整棵樹（遞歸 CTE，SQL Server / PostgreSQL / MySQL 8.0+）

WITH RECURSIVE MenuTree AS (
    -- 錨點：根節點
    SELECT MenuId, ParentId, Title, Url, 0 AS Level, CAST(SortOrder AS VARCHAR(MAX)) AS Path
    FROM Menu WHERE ParentId IS NULL
    UNION ALL
    -- 遞歸：子節點
    SELECT m.MenuId, m.ParentId, m.Title, m.Url, mt.Level + 1,
           mt.Path + ',' + CAST(m.SortOrder AS VARCHAR(MAX))
    FROM Menu m
    INNER JOIN MenuTree mt ON m.ParentId = mt.MenuId
)
SELECT * FROM MenuTree ORDER BY Path;

查詢指定節點的所有後代

WITH RECURSIVE SubTree AS (
    SELECT * FROM Menu WHERE MenuId = @RootId
    UNION ALL
    SELECT m.* FROM Menu m
    INNER JOIN SubTree st ON m.ParentId = st.MenuId
)
SELECT * FROM SubTree;

查詢節點到根的路徑（麵包屑）

WITH RECURSIVE Ancestors AS (
    SELECT * FROM Menu WHERE MenuId = @NodeId
    UNION ALL
    SELECT m.* FROM Menu m
    INNER JOIN Ancestors a ON m.MenuId = a.ParentId
)
SELECT * FROM Ancestors ORDER BY Level DESC;

大數據量優化：閉包表

CREATE TABLE MenuClosure (
    AncestorId INT NOT NULL REFERENCES Menu(MenuId),
    DescendantId INT NOT NULL REFERENCES Menu(MenuId),
    Depth INT NOT NULL,
    PRIMARY KEY (AncestorId, DescendantId)
);
-- 觸發器/應用層維護 Closure 表
-- 查詢後代：SELECT m.* FROM Menu m JOIN MenuClosure c ON m.MenuId=c.DescendantId WHERE c.AncestorId=@Id;
-- 查詢祖先：SELECT m.* FROM Menu m JOIN MenuClosure c ON m.MenuId=c.AncestorId WHERE c.DescendantId=@Id;

前端綁定建議

• 後端輸出扁平列表 + Level/Path，前端用遞歸組件渲染（Vue/React/Angular 均有成熟 Tree 組件）。
• 啟用懶加載：僅加載第一層，展開時 AJAX 請求子節點。
• 權限控制：在 CTE 中 JOIN 權限表過濾不可見節點。

MySQL 5.7 兼容方案

不支持 CTE，改用存儲過程遞歸或應用層多次查詢組裝。或升級至 MySQL 8.0 / MariaDB 10.2+。