數據庫設計：一級二級分類表結構與關聯查詢最佳實踐

方案一：自關聯表 (最常用，支持無限級)

CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INT DEFAULT 0, -- 0 表示頂級
    sort_order INT DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES category(id) ON DELETE SET NULL
);

-- 數據示例
INSERT INTO category (name, parent_id) VALUES 
('電子產品', 0), ('手機', 1), ('筆記本', 1),
('圖書', 0), ('小說', 4), ('教材', 4);

查詢所有子分類 (遞歸 CTE, MySQL 8.0+/PG/SQL Server)

WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id, 0 AS level FROM category WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM category c JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT * FROM cat_tree;

方案二：獨立映射表 (嚴格兩級、多對多)

CREATE TABLE category_l1 (id INT PK, name VARCHAR(100));
CREATE TABLE category_l2 (id INT PK, name VARCHAR(100));
CREATE TABLE category_map (l1_id INT, l2_id INT, PRIMARY KEY(l1_id,l2_id),
    FOREIGN KEY(l1_id) REFERENCES category_l1(id),
    FOREIGN KEY(l2_id) REFERENCES category_l2(id)
);

方案三：路徑枚舉 / 閉包表 (高性能讀取)

-- 閉包表
CREATE TABLE category_closure (
    ancestor INT, descendant INT, depth INT,
    PRIMARY KEY(ancestor, descendant)
);
-- 查詢所有後代：SELECT descendant FROM category_closure WHERE ancestor = 1 AND depth > 0;

選型建議