MySQL:类别树和n层产品

Question

因此，让我们假设我有一个MySQL DB，其中包含以下表：

产品

< li>id < li >名称 < li >类别id

类别

< li>id < li >名称 < li>parent_id

查询数据库的最佳方式是什么，以获得从某个类别id开始的所有产品。例如，如果我有一个子类别树，其中基本类别id=1，我如何获得id=1子类别下的所有产品，子类别的数量不确定。

我可以这样做：

SELECT * FROM `Product` WHERE category_id IN (
   SELECT `id` FROM `Category` WHERE parent_id = 1
)

但是，它仅适用于类别id=1的直接子级，而不适用于第二到n级子级。

非常感谢。

编辑

有人建议读一篇关于这方面的博客文章，我也看过那篇关于过去的文章，我做了这个sqlfiddle：

http://sqlfiddle.com/#!2/be72ec/1

正如您在查询中看到的，即使是他们教授的最简单的方法，获取类别树也不会输出任何内容。我错过了什么？其他方法也有同样的问题。

非常感谢。

Answer 1

我看了你的剧本，得出了以下结论。

代码的问题在于，您使用的是空值。看看这个sqlfiddle可以看到一个工作示例：http://sqlfiddle.com/#!2/07ef7/1/0

我对你的陈述做了一些更正，现在一切都正常了
以下是我所做更改的列表：

< li >在create语句中，将“< code > parent _ id bigint(11)unsigned DEFAULT NULL”更改为“< code > parent _ id bigint(11)unsigned NOT NULL DEFAULT 0” < li >删除外键约束，但我无法将“0”用作parent_id < li >插入“0”而不是“NULL”作为parent_id < li >将函数中的数据类型INT更改为BIGINT，因为您在creation语句中使用了此类型 < li >从select语句中删除WHERE子句

我希望这对你有帮助！

下面是一些语句，以防sqlfiddle现在不工作:

CREATE TABLE `Category` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `parent_id` bigint(11) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  KEY `ix_hierarchy_parent` (`parent_id`, `id`)
)//


INSERT INTO `Category` (`id`, `name`, `parent_id`)
VALUES
    (1,'Cat Name 1',0),
    (2,'Cat Name 2',0),
    (3,'Cat Name 3',2),
    (4,'Cat Name 4',2),
    (7,'Cat Name 5',4),
    (8,'Cat Name 6',4),
    (9,'Cat Name 7',4),
    (10,'Cat Name 8',4),
    (11,'Cat Name 9',4),
    (12,'Cat Name 10',4),
    (13,'Cat Name 11',2),
    (16,'Cat Name 12',13),
    (17,'Cat Name 13',13),
    (18,'Cat Name 14',13),
    (19,'Cat Name 15',13),
    (20,'Cat Name 16',13),
    (21,'Cat Name 17',13)//


CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value BIGINT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id BIGINT;
        DECLARE _parent_id BIGINT;
        DECLARE _next BIGINT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent_id = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    `Category`
                WHERE   parent_id = _parent_id
                        AND id > _id;
                IF @id IS NOT NULL OR _parent_id = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent_id
                INTO    _id, _parent_id
                FROM    `Category`
                WHERE   id = _parent_id;
        END LOOP;       
END//

SELECT  CONCAT(REPEAT('    ', level - 1), CAST(hi.id AS CHAR)) AS treeitem, hi.name, parent_id, level
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, `Category`
        WHERE   @id IS NOT NULL
        ) ho
JOIN    `Category` hi
ON      hi.id = ho.id