萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

PostgreSQL 遞迴 CTE 與 tablefunc 實現樹狀資料遍歷

PostgreSQL 原生遞迴查詢:WITH RECURSIVE

PostgreSQL 透過 WITH RECURSIVE(Common Table Expression)原生支援遞迴查詢,語法直觀、效能優異,是處理鄰接模型樹狀資料的首選方案。

測試資料(沿用上文國家層級表)

-- country(id, name)
-- country_relation(id, parentid)

方法一:WITH RECURSIVE 產生完整路徑

WITH RECURSIVE tmp_country(id, path) AS (
  -- 錨點查詢:根節點
  SELECT a.id, '/' || b.name AS path
  FROM country_relation a
  INNER JOIN country b ON a.id = b.id
  WHERE a.parentid IS NULL

  UNION ALL

  -- 遞迴查詢:子節點接在父路徑後
  SELECT a.id, q.path || '/' || b.name
  FROM country_relation a
  INNER JOIN tmp_country q ON q.id = a.parentid
  INNER JOIN country b ON a.id = b.id
)
SELECT path FROM tmp_country ORDER BY path;

輸出(16 列):

/Earth
/Earth/North America
/Earth/North America/Canada
/Earth/North America/Central America
/Earth/North America/Island Nations
/Earth/North America/United States
/Earth/North America/United States/Alabama
/Earth/North America/United States/Alaska
/Earth/North America/United States/Arizona
/Earth/North America/United States/Arkansas
/Earth/North America/United States/California
/Earth/South America
/Earth/Europe
/Earth/Asia
/Earth/Africa
/Earth/Australia

耗時約 3ms,效能極佳。

方法二:tablefunc 擴充套件的 connectby 函數

需先啟用擴充:CREATE EXTENSION tablefunc;

因 connectby 需要「名稱+父名稱」欄位,先建暫存表並填入父名稱:

CREATE TEMPORARY TABLE tmp_country_relation AS
SELECT b.id, a.name, b.parentid, ''::text AS parentname
FROM country a, country_relation b WHERE a.id = b.id;

UPDATE tmp_country_relation
SET parentname = a.name
FROM country a WHERE parentid = a.id;

呼叫 connectby

SELECT path FROM connectby(
  'tmp_country_relation',  -- 表名
  'name',                  -- 節點名稱欄位
  'parentname',            -- 父節點名稱欄位
  'Earth',                 -- 起始根節點
  0,                       -- 最大深度(0=不限)
  '/'                      -- 路徑分隔符
) AS t(id text, parentid text, level int, path text)
ORDER BY level;

輸出(16 列,順序依層級):

Earth
Earth/Australia
Earth/North America
Earth/Africa
Earth/South America
Earth/Europe
Earth/Asia
Earth/North America/Island Nations
Earth/North America/Canada
Earth/North America/Central America
Earth/North America/United States
Earth/North America/United States/California
Earth/North America/United States/Arkansas
Earth/North America/United States/Alabama
Earth/North America/United States/Alaska
Earth/North America/United States/Arizona

耗時約 6ms。

兩種方案比較

維度WITH RECURSIVEtablefunc.connectby
相依性核心功能,無需額外安裝需 CREATE EXTENSION tablefunc
彈性極高,可任意組合 JOIN、過濾、排序固定參數介面,較死板
效能優,查詢優化器支援完善良,但大樹可能較慢
適用場景通用遞迴查詢、複雜業務邏輯快速原型、相容 Oracle 遷移專案

建議

優先使用 WITH RECURSIVE,符合 SQL 標準、可維護性高;僅在需快速相容 Oracle CONNECT BY 語義時考慮 tablefunc。

copyright © 萬盛學電腦網 all rights reserved