mysql 查询分类下的所有商品

Question

寻求一个解决方案：
有一个分类，下面可能有无限级子分类，暂定5级子分类吧！那如果用户点击顶级分类，怎么查出该分类下的所有商品，这个顶级分类下的树形结构分类可能有上千个子分类，如果按照顶级分类查出所有子分类，然后通过mysql的catid去用IN查询，肯定会挂掉
请问各位大神如何解决这种需求，数据结构方面怎么设计呢？或者什么解决算法，用的是PHP

Answer 1

给个思路。以空间换时间，就是冗余。
举个例子,一下是分类

• 电器 cat_id = 1

  • 电脑 cat_id = 2 pid = 1

  • 手机 cat_id = 3 pid = 1

    • 苹果手机 cat_id =4 pid =3

那么 有个 商品 iphone 8 ,商品ID goods_id G1001

可以两种方案都可以搞定。
第一做中间表
cat_id goods_id
1 G1001
3 G1001
4 G1001
这样，如果你是到哪个分类下，都可以找到该商品

第二种做多级字段
catid_string 1,3,4
做查询的时候 比如说找电器 ， 只要 where catid_string LIKE "1%"
找手机 where catid_string LIKE "1,3%"

以上两种方法都可以适合做查询
但是第一种方法可以更好的做各种业务拓展，更有选择的余地。

Answer 2

闭包表，单独用一个表来记录全部的祖先关系。

CREATE TABLE `tree`  (
  `ancestor` int NOT NULL COMMENT '祖先ID（分类ID）',
  `descendant` int NOT NULL COMMENT '后代ID',
  `descendant_type` tinyint NOT NULL COMMENT '后代类型（0分类，1商品）',
  PRIMARY KEY (`ancestor`, `descendant`, `descendant_type`)
);

要查某个分类的下的全部商品id：

select descendant from tree where ancestor = 10 and descendant_type = 1;

要查出商品信息就关联查询：

select p.* from products p 
  join tree t on p.id = t.descendant 
  where t.ancestor = 10 and t.descendant_type = 1

当然，增删分类和商品的时候要修改闭包表。

《SQL反模式》里总结的一个表：

Answer 3

刚网上搜到一个比较不高大上的方法，但是我觉得可行，不知道还有没有比这更好的方法，就是所有子分类用一个字段记录他所有的父级id，有多少就记录多少，查询的时候用find_in_set,不过这个函数貌似很慢,继续关注大神们的回答

Answer 4

我们一般设计都是需要有一个字段记录他的上级id，如果是顶级就是0，然后递归查询无限极分类

Answer 5

如果注重查询效率的话可以考虑MPTT - Modified Preorder Tree Traversal