MySQL WITH RECURSIVE 详解

目录一、什么是 WITH RECURSIVE二、MySQL 从哪个版本开始支持三、它解决了什么问题组织架构菜单系统行政区划评论回复查询所有子节点四、WITH RECURSIVE 语法结构五、递归执行过程第一步第二步第三步六、WITH RECURSIVE 的组成部分1. Anchor锚点2. Recursive递归部分3. UNION ALL七、第一个实战生成数字序列生成1~10八、生成日期序列九、树形结构实战部门表数据结构十、递归向下查询查询所有子节点十一、增加层级显示十二、生成完整路径十三、WITH RECURSIVE 能向上查吗十四、向上递归查询祖先节点十五、向上生成完整路径十六、避免死循环十七、递归层数限制十八、WITH RECURSIVE 使用规则总结十九、企业开发中的典型应用组织架构树RBAC权限菜单评论回复行政区域商品分类二十、面试高频问题Q1WITH RECURSIVE 从哪个版本开始支持Q2WITH 和 WITH RECURSIVE 区别Q3能否查询父节点Q4为什么推荐 WITH RECURSIVE总结一、什么是 WITH RECURSIVEWITH RECURSIVE是 MySQL 提供的一种递归公共表表达式Recursive Common Table ExpressionRecursive CTE。简单理解它允许 SQL 自己调用自己实现递归查询。类似于 Java 中publicvoidtest(Nodenode){test(node.getChild());}SQL 以前无法直接递归只能多次自关联JOIN应用层循环查询存储过程递归而WITH RECURSIVE出现后可以直接在 SQL 中完成树形结构遍历。二、MySQL 从哪个版本开始支持MySQL 从MySQL 8.0开始正式支持CTE公共表表达式Recursive CTE递归公共表表达式即WITH...和WITHRECURSIVE...都是 MySQL 8.0 新增的特性。MySQL 5.7 及以前❌ 不支持三、它解决了什么问题开发中经常出现树形结构组织架构董事长 ├── 总经理 │ ├── 技术部 │ └── 财务部 └── 人事部菜单系统系统管理 ├── 用户管理 ├── 角色管理 └── 权限管理行政区划中国 ├── 北京 ├── 上海 └── 广东 ├── 深圳 └── 广州评论回复评论1 ├── 回复1 │ └── 回复2 └── 回复3以前查询查询所有子节点需要select*fromdeptwhereparent_id1;select*fromdeptwhereparent_idin(...);select*fromdeptwhereparent_idin(...);不断循环。现在WITHRECURSIVE一条 SQL 搞定。四、WITH RECURSIVE 语法结构标准语法WITHRECURSIVE cte_nameAS(-- 初始查询锚点查询SELECT...UNIONALL-- 递归查询SELECT...FROMtabletJOINcte_name cON...)SELECT*FROMcte_name;五、递归执行过程例如WITHRECURSIVE numsAS(SELECT1ASnUNIONALLSELECTn1FROMnumsWHEREn5)SELECT*FROMnums;执行步骤第一步执行锚点查询SELECT1结果1第二步带入递归部分11得到2第三步继续递归3 4 5结果1 2 3 4 5六、WITH RECURSIVE 的组成部分必须包含两部分1. Anchor锚点递归起点SELECT12. Recursive递归部分不断调用自身SELECTn1FROMnumsWHEREn53. UNION ALL连接两部分AnchorUNIONALLRecursive七、第一个实战生成数字序列生成1~10WITHRECURSIVE numsAS(SELECT1ASnumUNIONALLSELECTnum1FROMnumsWHEREnum10)SELECT*FROMnums;结果1 2 3 4 5 6 7 8 9 10八、生成日期序列生成最近7天WITHRECURSIVE datesAS(SELECTCURDATE()ASdtUNIONALLSELECTDATE_SUB(dt,INTERVAL1DAY)FROMdatesWHEREdtCURDATE()-INTERVAL6DAY)SELECT*FROMdates;结果2026-06-15 2026-06-14 2026-06-13 ...九、树形结构实战部门表CREATETABLEdept(idINTPRIMARYKEY,dept_nameVARCHAR(50),parent_idINT);数据1 总公司 NULL 2 技术中心 1 3 财务中心 1 4 开发部 2 5 测试部 2 6 Java组 4 7 前端组 4数据结构总公司(1) ├── 技术中心(2) │ ├── 开发部(4) │ │ ├── Java组(6) │ │ └── 前端组(7) │ └── 测试部(5) └── 财务中心(3)十、递归向下查询查询所有子节点查询部门1下面所有节点WITHRECURSIVE dept_treeAS(SELECTid,dept_name,parent_id,1levelFROMdeptWHEREid1UNIONALLSELECTd.id,d.dept_name,d.parent_id,dt.level1FROMdept dJOINdept_tree dtONd.parent_iddt.id)SELECT*FROMdept_tree;结果1 总公司 2 技术中心 3 财务中心 4 开发部 5 测试部 6 Java组 7 前端组十一、增加层级显示WITHRECURSIVE dept_treeAS(SELECTid,dept_name,parent_id,1levelFROMdeptWHEREid1UNIONALLSELECTd.id,d.dept_name,d.parent_id,dt.level1FROMdept dJOINdept_tree dtONd.parent_iddt.id)SELECTid,dept_name,levelFROMdept_tree;结果id dept_name level 1 总公司 1 2 技术中心 2 3 财务中心 2 4 开发部 3 5 测试部 3 6 Java组 4 7 前端组 4十二、生成完整路径很多权限系统都这样做。例如总公司/技术中心/开发部/Java组SQLWITHRECURSIVE dept_treeAS(SELECTid,dept_name,parent_id,dept_nameASpathFROMdeptWHEREid1UNIONALLSELECTd.id,d.dept_name,d.parent_id,CONCAT(dt.path,/,d.dept_name)FROMdept dJOINdept_tree dtONd.parent_iddt.id)SELECT*FROMdept_tree;结果总公司 总公司/技术中心 总公司/技术中心/开发部 总公司/技术中心/开发部/Java组十三、WITH RECURSIVE 能向上查吗答案完全可以。很多人误以为只能向下查。实际上递归方向由 JOIN 条件决定。十四、向上递归查询祖先节点例如查询 Java组(id6) 的所有上级。树总公司(1) └── 技术中心(2) └── 开发部(4) └── Java组(6)SQLWITHRECURSIVE parent_treeAS(SELECTid,dept_name,parent_idFROMdeptWHEREid6UNIONALLSELECTd.id,d.dept_name,d.parent_idFROMdept dJOINparent_tree ptONd.idpt.parent_id)SELECT*FROMparent_tree;结果6 Java组 4 开发部 2 技术中心 1 总公司十五、向上生成完整路径WITHRECURSIVE parent_treeAS(SELECTid,dept_name,parent_id,dept_nameASpathFROMdeptWHEREid6UNIONALLSELECTd.id,d.dept_name,d.parent_id,CONCAT(d.dept_name,/,pt.path)FROMdept dJOINparent_tree ptONd.idpt.parent_id)SELECT*FROMparent_treeORDERBYid;最终得到总公司/技术中心/开发部/Java组十六、避免死循环假设数据错误1 - 2 2 - 3 3 - 1形成环1 ↓ 2 ↓ 3 ↑ └───递归将无限执行。解决方法记录访问路径。WITHRECURSIVE dept_treeAS(SELECTid,parent_id,CAST(idASCHAR(1000))pathFROMdeptWHEREid1UNIONALLSELECTd.id,d.parent_id,CONCAT(dt.path,,,d.id)FROMdept dJOINdept_tree dtONd.parent_iddt.idWHEREFIND_IN_SET(d.id,dt.path)0)SELECT*FROMdept_tree;十七、递归层数限制查看SHOWVARIABLESLIKE%recursion%;通常cte_max_recursion_depth 1000表示最多递归1000层。修改SETSESSIONcte_max_recursion_depth5000;或者SETGLOBALcte_max_recursion_depth5000;十八、WITH RECURSIVE 使用规则总结必须WITHRECURSIVE nameAS(anchorUNIONALLrecursive)递归部分必须引用自己FROMname必须有终止条件WHERElevel100否则死循环。推荐使用UNIONALL而不是UNION因为UNION需要去重。性能更差。十九、企业开发中的典型应用组织架构树总公司 └── 分公司 └── 部门查询所有下级。RBAC权限菜单系统管理 ├── 用户管理 ├── 角色管理 └── 权限管理加载菜单树。评论回复评论 └── 回复 └── 回复查询完整评论链。行政区域中国 └── 广东 └── 深圳查询省市区。商品分类电子产品 └── 手机 └── 安卓手机查询所有分类。二十、面试高频问题Q1WITH RECURSIVE 从哪个版本开始支持MySQL 8.0。Q2WITH 和 WITH RECURSIVE 区别WITH普通CTE不递归。WITHtAS(SELECT*FROMuser)SELECT*FROMt;WITH RECURSIVE支持递归调用自身。Q3能否查询父节点可以。改变 JOIN 方向即可。向下d.parent_idtree.id向上d.idtree.parent_idQ4为什么推荐 WITH RECURSIVE相比循环查询SQL更简洁只访问一次数据库性能更好天然支持树形结构总结WITH RECURSIVE是 MySQL 8.0 引入的递归查询能力通过“锚点查询 UNION ALL 递归查询”的方式可以优雅地处理组织架构、菜单树、评论树、行政区划、商品分类等层级数据既能向下查询所有子孙节点也能向上查询所有祖先节点是现代 MySQL 树形数据查询的首选方案。参考mysql递归查询语法WITH RECURSIVEMySQL RECURSIVE ClausesMySQL | Recursive CTE (Common Table Expressions)