在許多實戰項目中，如電商系統、內容管理系統等，我們常常需要處理具有層級關係的數據，例如商品分類、文章欄目等。這些數據通常呈現出無限極分類的特點，即一個分類下可以有多個子分類，子分類下又可以有更深層次的子分類，層級關係複雜且不固定。下面將介紹一種適用於 MySQL 數據庫的無限極分類表設計，並對其設計思路、優缺點進行詳細分析，希望能為同行們提供一些有價值的參考。
表結構設計 我們設計的無限極分類表名為 category，其結構如下：
字段名 數據類型 是否為空 描述 id INT NOT NULL 分類唯一標識，主鍵 name VARCHAR(255) NOT NULL 分類名稱 parent_id INT YES 父分類 id，頂級分類為 0 level INT NOT NULL 分類級別，頂級為 1，二級為 2，以此類推 path VARCHAR(255) NOT NULL 分類路徑，用於快速查詢分類層級關係，格式如 “0-1-2-3” created_at DATETIME NOT NULL 創建時間 updated_at DATETIME NOT NULL 更新時間 設計思路 id：作為主鍵，唯一標識每個分類，方便進行關聯查詢等操作。
name：存儲分類的名稱，用於展示等場景。
parent_id：用於表示當前分類的父分類 id。頂級分類的 parent_id 為 0，這樣可以方便地區分頂級分類和其他分類。通過 parent_id 可以快速查詢到一個分類的直接上級分類，進而實現對分類層級的追溯。
level：直接存儲分類的級別，頂級分類為 1，二級分類為 2，以此類推。這樣在查詢時可以直接獲取到每個分類的級別，方便進行分類級別的篩選、統計等操作，比如統計不同級別分類的數量等。
path：分類路徑字段是一個關鍵設計點。它以 “0-1-2-3” 這種格式存儲分類的層級路徑，其中 “0” 代表頂級分類，“1” 是頂級分類下的第一個子分類，“2” 是 “1” 下的子分類，以此類推。通過這個路徑字段，可以快速判斷兩個分類之間的層級關係，比如判斷一個分類是否是另一個分類的子分類等。同時，也可以方便地查詢出一個分類的所有上級分類和下級分類。例如，要查詢 id 為 3 的分類的所有上級分類，就可以通過查找 path 中包含 “-3” 且 path 長度小於當前分類 path 長度的記錄來實現。
created_at 和 updated_at：記錄分類的創建時間和更新時間，方便進行數據的時間維度分析和管理。
索引設計 對 id 字段創建主鍵索引，這是數據庫自動創建的，用於快速定位單個分類記錄。
對 parent_id 字段創建普通索引。因為經常需要根據父分類 id 查詢子分類，比如查詢某個頂級分類下的所有二級分類等，索引可以大大提高這種查詢的效率。
對 level 字段創建普通索引。在進行分類級別相關的查詢和統計時，如查詢所有頂級分類、統計不同級別分類的數量等，索引可以加快查詢速度。
對 path 字段創建普通索引。由於 path 字段用於頻繁地查詢分類的層級關係，如查詢某個分類的所有上級分類或下級分類等，索引可以優化這些基於路徑的查詢操作。
示例數據 id name parent_id level path created_at updated_at 1 頂級分類 A 0 1 0-1 2024-01-01 10:00:00 2024-01-01 10:00:00 2 頂級分類 B 0 1 0-2 2024-01-02 11:00:00 2024-01-02 11:00:00 3 二級分類 A1 1 2 0-1-3 2024-01-03 12:00:00 2024-01-03 12:00:00 4 二級分類 B1 2 2 0-2-4 2024-01-04 13:00:00 2024-01-04 13:00:00 5 三級分類 A1-1 3 3 0-1-3-5 2024-01-05 14:00:00 2024-01-05 14:00:00 常見查詢示例 查詢所有頂級分類及其數量
sql 複製
SELECT COUNT(*) AS top_category_count FROM category WHERE parent_id = 0; 查詢某個頂級分類下的所有二級分類及數量
sql 複製
SELECT * FROM category WHERE parent_id = 1 AND level = 2; SELECT COUNT(*) AS second_category_count FROM category WHERE parent_id = 1 AND level = 2; 查詢某個分類下的所有子分類及級數
sql 複製
SELECT *, LENGTH(path) - LENGTH(REPLACE(path, '-', '')) AS sub_category_level FROM category WHERE path LIKE CONCAT((SELECT path FROM category WHERE id = 3), '%') AND id != 3; 優點 查詢效率高：通過 parent_id、level 和 path 字段的合理設計與索引優化，能夠快速實現對分類層級關係的各種查詢操作，如查詢某個分類的所有上級分類、下級分類，統計不同級別分類的數量等，大大提高了查詢效率。
結構清晰：表結構簡單明瞭，易於理解和維護。每個字段都有明確的含義和作用，方便開發人員進行數據操作和業務邏輯實現。
靈活性強：這種設計可以輕鬆應對分類層級的動態變化，無論是增加新的層級還是調整現有分類的層級關係，都能靈活處理，不會對錶結構造成大的影響。
擴展性好：在實際應用中，可以根據具體業務需求方便地對錶結構進行擴展，比如增加分類描述字段、分類狀態字段等，以滿足更多業務場景的要求。
缺點 數據冗餘：path 字段存儲了分類的層級路徑，這在一定程度上造成了數據冗餘。例如，一個深度為 5 的分類，其 path 字段會包含其所有上級分類的 id 信息，這可能會導致存儲空間的浪費，尤其是在分類數量較多且層級較深的情況下。
維護成本：當分類的層級關係發生變化時，如移動一個分類到另一個父分類下，需要更新該分類及其所有子分類的 parent_id 和 path 字段，這可能會涉及到較多的數據更新操作，增加了維護成本。不過，這種情況在實際業務中相對較少發生，且可以通過合理的業務流程和數據操作策略來降低影響。
path 字段長度限制：由於 path 字段使用 VARCHAR 類型存儲，其長度有一定限制。在極端情況下，如果分類層級過深，可能會導致 path 字段長度超過限制，從而引發數據插入或更新失敗的問題。不過，這種情況在正常業務場景下較為罕見，可以通過合理控制分類層級深度來避免。