您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 遞歸查詢樹狀表：存儲函數實現子節點/父節點全鏈路遍歷 (5.0~5.7 相容)

編輯：MySQL 綜合教學

背景

MySQL 5.x / 5.7 不支援 WITH RECURSIVE (CTE)，需用存儲函數 + 迴圈 + FIND_IN_SET 模擬遞歸遍歷。

表結構假設

CREATE TABLE `user_role` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(32),
  `parent_id` INT DEFAULT 0,  -- 0 表根節點
  INDEX `idx_parent` (`parent_id`)
) ENGINE=InnoDB;

1. 取得所有子節點 (含自身) — 遞歸向下

CREATE FUNCTION `get_child_list`(root_id INT)
RETURNS VARCHAR(1000)  -- 若節點極多請改 TEXT
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE child_list VARCHAR(1000) DEFAULT '';
  DECLARE child_temp VARCHAR(1000) DEFAULT CAST(root_id AS CHAR);

  WHILE child_temp IS NOT NULL AND child_temp <> '' DO
    IF child_list <> '' THEN
      SET child_list = CONCAT(child_list, ',', child_temp);
    ELSE
      SET child_list = child_temp;
    END IF;

    -- 找出下一層所有子節點
    SELECT GROUP_CONCAT(`id`) INTO child_temp
    FROM `user_role`
    WHERE FIND_IN_SET(`parent_id`, child_temp) > 0;
  END WHILE;

  RETURN child_list;
END;

調用方式

-- 取得 ID=2 的完整子樹 ID 列表
SELECT get_child_list(2);

-- 直接查出子樹明細
SELECT * FROM `user_role`
WHERE FIND_IN_SET(`id`, get_child_list(2)) > 0;

2. 取得所有父節點 (含自身) — 遞歸向上

CREATE FUNCTION `get_parent_list`(root_id INT)
RETURNS VARCHAR(1000)
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE parent_list VARCHAR(1000) DEFAULT '';
  DECLARE parent_temp VARCHAR(1000) DEFAULT CAST(root_id AS CHAR);

  WHILE parent_temp IS NOT NULL AND parent_temp <> '' DO
    -- 向上累加：新父節點放前面，保持從根到葉順序
    IF parent_list <> '' THEN
      SET parent_list = CONCAT(parent_temp, ',', parent_list);
    ELSE
      SET parent_list = parent_temp;
    END IF;

    -- 找出上一層所有父節點
    SELECT GROUP_CONCAT(`parent_id`) INTO parent_temp
    FROM `user_role`
    WHERE FIND_IN_SET(`id`, parent_temp) > 0;
  END WHILE;

  RETURN parent_list;
END;

調用方式

SELECT get_parent_list(6);  -- 從葉節點向上找到根
SELECT * FROM `user_role`
WHERE FIND_IN_SET(`id`, get_parent_list(6)) > 0;

效能與限制

項目	說明
適用版本	MySQL 5.0.94 ~ 5.7 / MariaDB 10.0+
最大深度	受限於 `group_concat_max_len` (預設 1024) 與回傳 VARCHAR 長度
大樹優化	改用 `TEXT` 回傳、調大 `group_concat_max_len`、或改寫為應用層遞歸
並發安全	函數為 `READS SQL DATA`，無鎖爭用
8.0+ 建議	直接用 `WITH RECURSIVE cte AS (...) SELECT ...`，標準、高效、可維護

替代方案：應用層遞歸 (推薦生產)

# Python 範例
def get_children(conn, root_id):
    stack, result = [root_id], []
    while stack:
        pid = stack.pop()
        result.append(pid)
        rows = conn.execute("SELECT id FROM user_role WHERE parent_id=%s", (pid,))
        stack.extend(r[0] for r in rows)
    return result

優點：無資料庫函數依賴、易除錯、支援無限深度、可緩存。缺點：多次往返 DB (可用 IN 批次查詢優化)。