MySQL递归获取商品分类以及所有下级分类商品

现在就是我们有一张商品表，还有一张分类表，商品表有一个字段用来记录当前商品分类id的，然后查询时候因为分类是有层级的，如果我们想通过顶层分类获取到它和所有子类的商品时就会需要用到MySQL的递归查询了。

首先是针对 MySQL 8.0 及以上版本，因为已经自带了 WITH RECURSIVE 递归函数，所以查询时简单很多，直接看 SQL

WITH RECURSIVE category_tree AS (
    SELECT id
    FROM category
    WHERE id = 1  -- 起始分类ID
    UNION ALL
    SELECT c.id
    FROM category c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT g.*
FROM goods g
INNER JOIN category_tree ct ON g.category_id = ct.id;

看一下查询结果

这么写可能有些人看着有些奇怪，我们换个方式就好理解一些

WITH RECURSIVE category_tree AS (
    SELECT id
    FROM category
    WHERE id = 1  -- 起始分类ID
    UNION ALL
    SELECT c.id
    FROM category c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
) SELECT id FROM category_tree

从下面的查询结果可以看到 WITH RECURSIVE 可以通过顶层的分类 id 获取其所有的下级分类 id，拿到这些 id 自然就可以查询到它所有的商品

这样我们在代码里面就可以这样写

上面说了 WITH RECURSIVE 是 MySQL 8.0 才有的函数，那么 MySQL 8.0 以下有没有办法查，答案当然是有的

SELECT g.*
FROM goods g
WHERE g.category_id IN
(SELECT 
    T2.id
FROM 
    (SELECT @r AS _id, 
            @r := (SELECT id FROM category WHERE parent_id = _id LIMIT 1) AS id
     FROM 
            (SELECT @r := 1) vars,
            category
     WHERE 
            @r IS NOT NULL) T1 
JOIN 
    category T2 
ON 
    T1._id = T2.parent_id OR T1._id = T2.id
ORDER BY 
    T2.id);

可以看到查询结果是一样的

关于这种递归我之前就写过一篇博客，大家有兴趣可以去看一下 MySQL通过SQL语句进行递归查询