mysql 递归遍历无限层级

MySQL 递归查询商品分类（无限级：下级+下下级）

一、测试数据表结构（商品分类）

搭建无限层级商品分类数据表，结构简洁通用，适用于商品分类、部门、地区等树形场景：

-- 商品分类表（无限级分类结构）
CREATE TABLE `category`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) COMMENT '分类名称',
  `parent_id` int NOT NULL DEFAULT 0 COMMENT '父分类ID，0=顶级分类',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

二、核心需求

通过MySQL递归语法，批量查询数据库中每个分类的直接下级与下下级分类，支持无限级递归遍历树形结构数据。

三、MySQL 递归查询语句（WITH RECURSIVE）

基于 MySQL8.0+ 支持的 WITH RECURSIVE 递归语法，实现无限层级分类查询，自动区分层级深度：

-- 递归查询所有分类的下级 + 下下级（无限级）
WITH RECURSIVE category_demo(
  top_id,        -- 顶级分类ID
  top_name,      -- 顶级分类名称
  child_id,      -- 下级/下下级分类ID
  child_name,    -- 下级/下下级分类名称
  depth          -- 层级深度（1=直接下级，2=下下级...）
) AS (
  -- 1. 基础查询：获取所有分类的直接下级（层级=1）
  SELECT
    a.id AS top_id,
    a.name AS top_name,
    b.id AS child_id,
    b.name AS child_name,
    1 AS depth
  FROM category a
  JOIN category b ON a.id = b.parent_id

  UNION ALL

  -- 2. 递归查询：获取下级的下级（层级递增）
  SELECT
    c.top_id,
    c.top_name,
    d.id AS child_id,
    d.name AS child_name,
    c.depth + 1 AS depth
  FROM category_demo c
  JOIN category d ON c.child_id = d.parent_id
)

-- 最终结果：按顶级分类 + 层级排序，数据规整有序
SELECT * FROM category_demo
ORDER BY top_id ASC, depth ASC, child_id ASC;

四、语句详细说明

1. 递归语法适配：采用 WITH RECURSIVE 标准递归语法，仅支持 MySQL 8.0 及以上版本，专门用于处理无限层级树形数据。
2. 基础查询层：首次执行查询，匹配所有顶级分类与其对应的直接下级分类，统一标记层级深度为1。
3. 递归遍历层：基于上一轮查询结果，持续递归关联数据表，查询子分类的下级数据，层级深度自动累加，实现无限层级遍历。
4. 通用应用场景：可适配商品多级分类、企业部门组织架构、省市地区区划、菜单权限等所有树形结构数据查询场景。

五、查询效果说明

• depth=1：代表当前顶级分类的一级直接下级分类
• depth=2：代表当前顶级分类的二级下下级分类
• 无需手动编写多表联查语句，自动遍历所有嵌套子层级，代码简洁、复用性强、查询效率高