无限级分类的原理及实现

目标：以 MySQL 为例实现一个无限级分类，并应用在一个产品表上，大概上就是一个在线商城系统里使用的无限级分类的产品模块。

这是两个相对独立的子问题：构建分类树本身结构，把分类树应用在产品表上。

无限级分类，是一个树状结构，某一个分类可能有多个子分类，但只有一个上级分类，最顶级分类没有上级。在数据结构上，通过两个数值表示：（当前分类id号，对应上级分类id号）。

从产品表入手。一个产品，对应一个所属的分类，而这个分类可能有上级分类、上上级分类… 如果需要查询某个分类有哪些产品，那么该分类所有下级分类、下下级分类…. 都要查询出来。所以，最直观的方式就是 查出每个分类的所有下级分类 id 号，拼接成 cate_id IN(id1, id2,id3,…) 这样的语句，查询效率也还是相当高的。如果我们深入思考一下，如果构建一张表，存储了每个分类的所有子分类，相当于把 IN 语句拆分成多行，然后使用JOIN 表查询。这个把所有对应关系”摊平“在一起的表，有个专门的名字，叫”闭包表“，也就是后文主要内容。

闭包表的维护有很多方法，下面是一种方法（其实是两种），适合半手工的处理，稍微改造即可自动化。

-- 0 分类表结构
CREATE TABLE `product_categories` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `parent_id` int(10) unsigned DEFAULT 0 COMMENT '父分类ID(0=顶级)',
    `name` varchar(100) NOT NULL COMMENT '分类名称',
    `slug` varchar(100) NOT NULL COMMENT 'URL标识符',
    `sort_order` int(11) DEFAULT 0 COMMENT '排序(越小越靠前)',
    `is_active` smallint(6) DEFAULT 1 COMMENT '1=启用 0=禁用',
    `level` smallint(6) DEFAULT 0 COMMENT '层级深度(从1起，0=未计算)',
    `path` varchar(500) DEFAULT '' COMMENT '路径ID链(如: 0,1,3)',
    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`),
    KEY `idx_parent_id_sort_order` (`parent_id`,`sort_order`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
;


-- 1 人工预览数据，是否有孤儿节点
-- // 如下的查询预期 0 行，否则要做数据修正
SELECT a.*
FROM `product_categories` a LEFT JOIN `product_categories` b ON a.parent_id=b.id
WHERE b.`id` IS NULL AND a.parent_id<>0;
;


-- 2 清理字段，准备初始环境
UPDATE `product_categories` SET level=0,path='';
UPDATE `product_categories` SET level=1, path=CONCAT(parent_id,',',id) WHERE parent_id=0;


-- 3 从根节点填充 level, path 字段
-- //多次执行如下语句，直到影响条数为0
UPDATE `product_categories` a INNER JOIN `product_categories` b ON a.parent_id=b.id
SET a.level=b.level+1, a.path=CONCAT(b.path,',',a.id)
WHERE b.`level`>0 AND a.`level`=0
;

-- 4 检查是否存在异常数据（不可达节点/隐藏孤岛）
-- //预期是0条，否则即是异常数据，可能造成闭包表构建失败。
--  不过，后面的步骤里忽略了这种 level=0 的行，不会失败，但结果中肯定缺失相关分类的数据
SELECT * FROM `product_categories` WHERE level=0;


-- [注] 下面5-8 构造闭包表，还有一个更易理解的版本，放在后面的附记9中
-- 5 准备闭包表，并把所有分类与父级的对应关系插入表中
-- // 闭包表存储了一组**所有可能**的对应关系，即每个分类从其自身、通过“找父级”关联到的分类，可以是0步或多步
DROP TABLE IF EXISTS cate_closure ;
CREATE TABLE `cate_closure` (
    `id` int(10) unsigned NOT NULL,
    `ancestor_id` int(10) unsigned COMMENT '祖先分类id',
    `depth` smallint NOT NULL COMMENT '层级间距/gaps',
    UNIQUE KEY (`id`,`ancestor_id`),
    KEY (`ancestor_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
;

-- 6 所有分类导入到闭包表
-- // 并设置所有分类id的祖先为其自身id，即经过0层到达（层间距离为0）
INSERT INTO `cate_closure`(`id`, `ancestor_id`, `depth`)
SELECT `id`, `id`,0 FROM `product_categories` WHERE 1
;

-- 7 所有分类id通过 1 步可以到达的祖先分类 （这步只是讲解原理，实际并不需要）
-- // 7.1 预览基于当前表再向上扩展一层的祖先关系，重点关注 a.id, b.parent_id, depth 三列
SELECT a.*,'|',b.*,'||',a.depth+1 as depth
FROM `cate_closure` a INNER JOIN `product_categories` b ON a.ancestor_id=b.id
WHERE b.parent_id > 0 AND b.level > 0
;

-- // 7.2 上条所述的3列就是需求的记录信，把它插入到 cate_closure 表中即可。
--   即 INSERT INTO `cate_closure`(`id`, `ancestor_id`, `depth`) ...  (语句略)

-- // 7.3 这时 cate_closure 表就是经过0层及1层可达的对应关系。

-- // 7.4 如果再执行一次 7.1 的语句，其查询结果就是经过 1步或2步关联到的分类。 
--   当然，可以通过 a.depth=1 筛选出的就是第2步关联到的所有分类。
--   不过，我们可以施展个更巧妙的手段，不做限制，而是把查询结果记录集与 cate_closure 表做去重，即

-- // 7.5  像这样
SELECT a.id, b.parent_id as ancestor_id, a.depth+1 as depth
FROM `cate_closure` a INNER JOIN `product_categories` b ON a.ancestor_id=b.id
    LEFT JOIN `cate_closure` c ON c.id=a.id AND c.ancestor_id=b.parent_id
WHERE b.parent_id > 0 AND b.level > 0
    AND c.id IS NULL
;

-- // 总结 7.9
-- 到此拆解了这条复杂的语句，可以不动脑子的拿它多次执行

-- 8 循环执行下面语句，直到影响条数为0行
INSERT INTO `cate_closure`(`id`, `ancestor_id`, `depth`) 
SELECT a.id, b.parent_id as ancestor_id, a.depth+1 as depth
FROM `cate_closure` a INNER JOIN `product_categories` b ON a.ancestor_id=b.id
    LEFT JOIN `cate_closure` c ON c.id=a.id AND c.ancestor_id=b.parent_id
WHERE b.parent_id > 0 AND b.level > 0
    AND c.id IS NULL
;


-- 9 附记
-- 事实上，循环执行的语句确实比较晦涩。还有一个易于理解的方式，替代前面 5-8 几步
-- 从定义出发，即从前面 5 所述
--    每个分类从其自身、通过“找父级”关联到的分类，可以是0步或多步
-- 写一系列语句，逐层处理成一张表，然后合并，事实上这就是“手工模拟了递归”
--  这很清晰直观、很容易理解；但，
--     每层都要写一条CREATE语句，还要合并，稍不注意会漏掉
--     记得清理临时表 cate_closure_pN, 还有为简洁 cate_closure 表的索引省略了
/*
*/

CREATE TABLE cate_closure_p0
SELECT `id`, `id` as ancestor_id,0 as depth FROM `product_categories` WHERE 1
;
CREATE TABLE cate_closure_p1
SELECT a.id, b.parent_id as ancestor_id, a.depth+1 as depth
FROM `cate_closure_p0` a INNER JOIN `product_categories` b ON a.ancestor_id=b.id
WHERE b.parent_id > 0 AND b.level > 0
;
CREATE TABLE cate_closure_p2
SELECT a.id, b.parent_id as ancestor_id, a.depth+1 as depth
FROM `cate_closure_p1` a INNER JOIN `product_categories` b ON a.ancestor_id=b.id
WHERE b.parent_id > 0 AND b.level > 0
;
CREATE TABLE cate_closure_p3
SELECT a.id, b.parent_id as ancestor_id, a.depth+1 as depth
FROM `cate_closure_p2` a INNER JOIN `product_categories` b ON a.ancestor_id=b.id
WHERE b.parent_id > 0 AND b.level > 0
;
... -- 这里要写一系列语句，总数量可参考 SELECT MAX(level) FROM product_categories;

CREATE TABLE cate_closure
SELECT * FROM cate_closure_p0
UNION ALL 
SELECT * FROM cate_closure_p1
UNION ALL 
SELECT * FROM cate_closure_p2
UNION ALL 
SELECT * FROM cate_closure_p3
... -- 同上
;

附件： eg_categories.sql

Last Updated on 2026/08/06