遞歸 CTE 解決父子級 ID 關聯更新：MySQL/SQL Server 通用寫法與遊標對比

經典需求：表 AreaRegion(AreaCode, AreaName, ParentCode) 存儲樹狀結構，需新增 Content 欄位，將每節點的所有祖先名稱按層級拼接 (如 越城區,紹興市,浙江省) 寫入。對比遊標迴圈與遞歸 CTE (Common Table Expression) 兩種方案。

表結構與樣本資料

CREATE TABLE AreaRegion (
    AreaCode VARCHAR(10) NOT NULL,
    AreaName VARCHAR(50),
    ParentCode VARCHAR(10),
    Content NVARCHAR(200)
);
-- 根節點 ParentCode='0'
INSERT INTO AreaRegion VALUES ('110000','北京市','0',NULL);
INSERT INTO AreaRegion VALUES ('110100','東城區','110000',NULL);
-- ... 共 3000 條

方案一：遞歸 CTE (推薦、集合化、高效)

支援：MySQL 8.0+、SQL Server、PostgreSQL、Oracle。

WITH cte(AreaCode, AreaName, Content) AS (
    -- 錨點：根節點
    SELECT AreaCode, AreaName, CAST(AreaName AS VARCHAR(500)) AS Content
    FROM AreaRegion
    WHERE ParentCode = '0'
    UNION ALL
    -- 遞歸：子節點拼接父 Content
    SELECT a.AreaCode, a.AreaName,
           CAST(c.Content + ',' + a.AreaName AS VARCHAR(500))
    FROM AreaRegion a
    INNER JOIN cte c ON a.ParentCode = c.AreaCode
)
-- 更新回原表
UPDATE a
SET a.Content = c.Content
FROM AreaRegion a
INNER JOIN cte c ON a.AreaCode = c.AreaCode;

優點： 單條 SQL、無需臨時表/遊標、優化器可並行、可維護性強。

方案二：遊標 + 遞歸函數 (舊版本相容、程序化)

-- 1. 遊標遍歷所有 AreaCode
DECLARE cur CURSOR FOR SELECT AreaCode FROM AreaRegion;
OPEN cur;
FETCH NEXT FROM cur INTO @code;

-- 2. 表變數存儲中間結果
DECLARE @tmp TABLE (AreaCode INT PRIMARY KEY, Content NVARCHAR(3000));

WHILE @@FETCH_STATUS = 0 BEGIN
    -- 3. 遞歸 CTE 取單節點祖先鏈 (舊版本可用遞歸函數/WHILE 迴圈)
    WITH tb AS (
        SELECT *, 0 AS lvl FROM AreaRegion WHERE AreaCode = @code
        UNION ALL
        SELECT b.*, lvl+1 FROM tb a INNER JOIN AreaRegion b ON a.ParentCode = b.AreaCode
    )
    SELECT @content = COALESCE(@content + ',', '') + AreaName
    FROM tb ORDER BY lvl ASC;

    INSERT INTO @tmp VALUES (@code, @content);
    SET @content = '';
    FETCH NEXT FROM cur INTO @code;
END
CLOSE cur; DEALLOCATE cur;

-- 4. 關聯更新
UPDATE a SET a.Content = b.Content
FROM AreaRegion a INNER JOIN @tmp b ON a.AreaCode = b.AreaCode;

缺點： RBAR (Row By Agonizing Row)、上下文切換開銷大、難以並行、代碼冗長。

方案三：應用層緩存 + 批次更新 (超大表/跨庫)

1. 一次性 SELECT AreaCode, AreaName, ParentCode FROM AreaRegion 讀入記憶體 (3000 行極小)。
2. 應用層 (Java/Python/Go) 建立 Map<String, Node>，遞歸組裝 Content。
3. 批次 UPDATE 或 LOAD DATA 寫回。

優點： 邏輯靈活、不佔用資料庫 CPU、易單測、跨異構資料庫通用。

效能對比 (3000 行測試)

最佳實踐建議

1. MySQL 8.0+/SQL Server 2008+ 首選 遞歸 CTE 單條 UPDATE。
2. 舊版本 MySQL 5.7 或無 CTE 支援，採用 應用層緩存組裝，避免遊標。
3. 超大表 (百萬級) 分批次 CTE 或應用層分頁處理，避免長事務鎖表。
4. 新增 Content 欄位建議加索引 (若頻繁查詢) 或全文索引 (模糊搜尋)。

集合化思維 (CTE) 勝過程序化思維 (Cursor)，是現代 SQL 開發的核心素養。