MySQL/MariaDB递归查询自身及所有子类均无商品的分类方法

现有结构与需求说明 #

• 涉及两张表：
  • category分类表：字段为id_category(分类ID)、id_parent(父分类ID)、level_depth(分类层级)
  • category_product分类商品关联表：字段为id_category(分类ID)、id_product(商品ID)
• 核心需求：将自身无关联商品，且所有后代子分类也均无关联商品的分类的active字段设为0；只要分类自身或任意后代存在关联商品，就保留active=1
• 原有SQL问题：仅判断分类自身是否关联商品，会误禁用「自身无商品但子分类有商品」的父分类
• 原有递归尝试的问题：递归方向为从无商品分类向下找无商品子分类，既存在语法错误（CTE名拼写前后不一致、join all应为union all、递归锚点部分语法结构错误），也无法识别分支下层存在有商品分类的场景。

方案1：递归CTE实现（推荐，支持MySQL8.0+、PostgreSQL等数据库） #

实现逻辑 #

反向遍历比正向遍历逻辑更简单、准确率更高：

1. 锚点成员：查询所有自身直接关联商品的分类ID，这部分分类肯定不能禁用
2. 递归成员：从上述分类出发，向上递归查找所有父级、祖父级祖先分类，这些分类因为后代存在商品，也不能禁用
3. 最终要禁用的分类：所有不在上述「不可禁用分类集合」中的分类

代码实现 #

先查询验证目标分类：

WITH RECURSIVE cannot_disable AS (
    -- 锚点：所有自身绑定了商品的分类
    SELECT DISTINCT cp.id_category
    FROM `category_product` cp
    UNION ALL
    -- 递归向上找所有祖先分类
    SELECT c.id_parent
    FROM `category` c
    JOIN cannot_disable cd ON c.id_category = cd.id_category
    WHERE c.id_parent != 0 -- 排除根节点的虚拟父ID0，避免递归死循环
)
SELECT id_category 
FROM `category`
WHERE id_category NOT IN (SELECT id_category FROM cannot_disable);

适配现有PrestaShop环境的更新语句：

Db::getInstance()->execute('
WITH RECURSIVE cannot_disable AS (
    SELECT DISTINCT cp.id_category
    FROM `'._DB_PREFIX_.'category_product` cp
    UNION ALL
    SELECT c.id_parent
    FROM `'._DB_PREFIX_.'category` c
    JOIN cannot_disable cd ON c.id_category = cd.id_category
    WHERE c.id_parent != 0
)
UPDATE `'._DB_PREFIX_.'category` 
SET `active` = 0 
WHERE `id_category` NOT IN (SELECT id_category FROM cannot_disable)
');

示例数据验证 #

基于给出的示例数据：

• 自身绑定商品的分类是ID=2、ID=20
• 递归找到的祖先分类是ID=1（ID=2的父分类）
• 不可禁用集合为[1,2,20]
• 最终返回的待禁用分类为[21,22,30]，完全符合需求：21无商品也无后代，22和其子分类30都无商品。

方案2：非递归实现（适配不支持CTE的旧版MySQL） #

分类表自带level_depth字段，已知最大分类层级的前提下，可以通过多层自连接实现，不需要递归：

// 示例按最大层级为3编写，可根据实际最大分类层级扩展LEFT JOIN层数
Db::getInstance()->execute('
UPDATE `'._DB_PREFIX_.'category` c
LEFT JOIN `'._DB_PREFIX_.'category_product` cp1 ON cp1.id_category = c.id_category
LEFT JOIN `'._DB_PREFIX_.'category` c1 ON c1.id_parent = c.id_category
LEFT JOIN `'._DB_PREFIX_.'category_product` cp2 ON cp2.id_category = c1.id_category
LEFT JOIN `'._DB_PREFIX_.'category` c2 ON c2.id_parent = c1.id_category
LEFT JOIN `'._DB_PREFIX_.'category_product` cp3 ON cp3.id_category = c2.id_category
SET c.active = 0
WHERE cp1.id_category IS NULL 
  AND cp2.id_category IS NULL 
  AND cp3.id_category IS NULL
');

逻辑说明：通过左连接依次关联当前分类、子分类、孙分类的商品关联记录，如果所有关联结果都为空，说明当前分类和所有后代都没有绑定商品，可以禁用。如果分类最大层级更深，按照相同规则继续追加LEFT JOIN子分类、LEFT JOIN对应层级商品表的语句，在WHERE条件里补充对应cp字段的空判断即可。

内容的提问来源于stack exchange，提问作者TheCHE