mysql 8.0引入遞歸cte以支持層級數(shù)據(jù)查詢。遞歸cte是一種可調(diào)用自身的公共表表達式，適用于樹形或圖結(jié)構(gòu)數(shù)據(jù)處理，基本結(jié)構(gòu)包括初始查詢與遞歸部分并通過union all連接。1. 可用于查詢組織結(jié)構(gòu)中的所有下屬員工；2. 構(gòu)建目錄樹結(jié)構(gòu)如商品分類；3. 使用時需注意啟用mysql 8.0及以上版本、避免無限循環(huán)并設(shè)置最大深度限制；4. 建議對parent_id字段建立索引以提升性能。遞歸cte為處理層級結(jié)構(gòu)提供高效方案，但需關(guān)注版本兼容性及細節(jié)控制。
在MySQL中處理層級數(shù)據(jù)，比如組織架構(gòu)、目錄樹、評論回復等場景時，遞歸查詢是個很實用的功能。早期的MySQL版本并不支持遞歸查詢，只能通過多次查詢或者程序邏輯來實現(xiàn)。但從MySQL 8.0開始，引入了遞歸CTE（Common table Expression）語法，使得這類問題可以在SQL層面高效解決。
什么是遞歸CTE？
遞歸CTE是一種可以調(diào)用自身的公共表表達式，它特別適合用來處理樹形結(jié)構(gòu)或圖結(jié)構(gòu)的數(shù)據(jù)。基本結(jié)構(gòu)包括一個初始查詢和一個遞歸部分，兩者通過UNION ALL連接。
舉個簡單的例子，如果你有一個部門表departments，字段包括id和parent_id，你想查出某個部門及其所有子部門的信息，就可以使用遞歸CTE來完成。
WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id FROM departments WHERE id = 1 -- 初始節(jié)點，比如根部門 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN dept_tree t ON d.parent_id = t.id ) SELECT * FROM dept_tree;
這個語句會從id=1的部門開始，不斷查找其下級部門，直到?jīng)]有更多子節(jié)點為止。
遞歸CTE的典型應(yīng)用場景
查詢組織結(jié)構(gòu)中的所有下屬員工
假設(shè)你有一個員工表employees，其中包含字段employee_id和manager_id，你想找出某個領(lǐng)導下的所有下屬員工（包括間接下屬），這時候遞歸CTE就能派上用場。
WITH RECURSIVE subordinates AS ( SELECT employee_id, name, manager_id FROM employees WHERE manager_id = 100 -- 查找直接下屬 UNION ALL SELECT e.employee_id, e.name, e.manager_id FROM employees e INNER JOIN subordinates s ON e.manager_id = s.employee_id ) SELECT * FROM subordinates;
這種寫法能一次性獲取完整的下屬鏈條，比循環(huán)查詢效率高很多。
構(gòu)建目錄樹結(jié)構(gòu)
文件系統(tǒng)或商品分類常采用父子結(jié)構(gòu)存儲。例如，你有一個categories表，里面有category_id和parent_id字段。要展示某一級分類下的完整子樹結(jié)構(gòu)，也可以用遞歸CTE：
WITH RECURSIVE category_tree AS ( SELECT category_id, name, parent_id FROM categories WHERE category_id = 5 UNION ALL SELECT c.category_id, c.name, c.parent_id FROM categories c INNER JOIN category_tree t ON c.parent_id = t.category_id ) SELECT * FROM category_tree;
這樣就能拿到該分類及其所有子分類的信息，方便前端渲染成樹狀結(jié)構(gòu)。
使用遞歸CTE需要注意的地方
-
必須啟用MySQL 8.0及以上版本：遞歸CTE是MySQL 8.0才引入的功能，老版本不支持。
-
避免無限循環(huán)：如果數(shù)據(jù)中存在環(huán)（比如A的父級是B，B的父級又是A），會導致遞歸無法終止。可以通過設(shè)置最大深度限制：
SET LOCAL statement_timeout = '60s';
或者在查詢中加一個層級字段，控制遞歸層數(shù)：
WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id, 1 AS level FROM departments WHERE id = 1 UNION ALL SELECT d.id, d.name, d.parent_id, t.level + 1 FROM departments d INNER JOIN dept_tree t ON d.parent_id = t.id WHERE t.level < 10 -- 控制最多遞歸10層 ) SELECT * FROM dept_tree;
-
性能優(yōu)化：遞歸查詢可能會涉及大量數(shù)據(jù)遍歷，建議對parent_id字段建立索引以提升效率。
總結(jié)一下
遞歸CTE為MySQL處理層級結(jié)構(gòu)提供了簡潔高效的解決方案。只要理解了它的基本結(jié)構(gòu)和常見用途，在實際開發(fā)中就能輕松應(yīng)對像組織架構(gòu)、目錄樹、評論嵌套等常見場景。當然，也要注意版本兼容性和潛在的性能問題。
基本上就這些，用起來不復雜但容易忽略細節(jié)，特別是層級控制和索引優(yōu)化方面。
