MySQL 父子遞歸樹結構排序：自定義函數 getPriority 實現無限級分類有序輸出

問題場景

表 category 存儲無限級分類 (id, parent_id, name)。需按**層級順序**輸出：父節點在前，子節點緊跟其後，同級按 ID 排序。

CREATE TABLE category (
    id INT(10) PRIMARY KEY,
    parent_id INT(10) DEFAULT 0,
    name VARCHAR(50)
);

INSERT INTO category VALUES
(1, 0, 'pizza'),       -- 根
(2, 0, 'burger'),      -- 根
(3, 0, 'coffee'),      -- 根
(4, 1, 'piperoni'),    -- 1.1
(5, 1, 'cheese'),      -- 1.2
(6, 1, 'vegetariana'), -- 1.3
(7, 5, 'extra cheese');-- 1.2.1

解決方案：生成「路徑編碼」排序

思路：為每個節點生成「祖先 ID 路徑」字符串，如 1.5.7，按字符串排序即得樹形順序。

1. 創建函數 getPriority

DELIMITER $$
CREATE FUNCTION getPriority (inID INT) 
RETURNS VARCHAR(255) 
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE gParentID INT DEFAULT 0;
    DECLARE gPriority VARCHAR(255) DEFAULT '';
    
    SET gPriority = CAST(inID AS CHAR); -- 起始為自身 ID
    SELECT parent_id INTO gParentID FROM category WHERE id = inID;
    
    WHILE gParentID > 0 DO  -- 0 為根節點標識
        SET gPriority = CONCAT(gParentID, '.', gPriority);
        SELECT parent_id INTO gParentID FROM category WHERE id = gParentID;
    END WHILE;
    
    RETURN gPriority;
END$$
DELIMITER ;

2. 查詢並排序

SELECT id, parent_id, name, getPriority(id) AS path_code
FROM category
ORDER BY path_code;

3. 結果示例

函數限制與優化

現代最佳實踐替代方案

方案 A：物化路徑 — 最推薦通用方案

ALTER TABLE category ADD COLUMN path VARCHAR(500) NOT NULL DEFAULT '';
-- 插入時維護：new_path = parent.path + '.' + new_id
-- 查詢：ORDER BY path
-- 祖先查詢：WHERE path LIKE '1.5.%'
-- 子孫查詢：WHERE path = '1.5' OR path LIKE '1.5.%'

方案 B：閉包表 — 查詢最強、寫入稍重

CREATE TABLE category_closure (
    ancestor INT NOT NULL,
    descendant INT NOT NULL,
    depth INT NOT NULL,
    PRIMARY KEY (ancestor, descendant)
);
-- 所有祖先-後代關係預計算，含自身 depth=0
-- 查詢所有子孫：SELECT descendant FROM category_closure WHERE ancestor=5 AND depth>0

方案 C：CTE 遞歸查詢 (MySQL 8.0+ / MariaDB 10.2+)

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name, CAST(id AS CHAR(255)) AS path, 1 AS level
    FROM category WHERE parent_id = 0
    UNION ALL
    SELECT c.id, c.parent_id, c.name, CONCAT(cte.path, '.', c.id), cte.level + 1
    FROM category c JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte ORDER BY path;

總結

1. getPriority 函數適合**小數據量、臨時查詢、不想改表結構**的場景。
2. **生產環境首選物化路徑**，讀寫性能均衡，SQL 簡單，支持所有 MySQL 版本。
3. MySQL 8.0+ 直接用 **CTE 遞歸**，零改造、標準 SQL、性能可接受。
4. 高頻讀寫、複雜樹操作 (移動子樹) 考慮 **閉包表**。