mysql - 树行为如何在此表中工作

Question

这是一个 Cakephp 树行为示例。我们可以在此表中存储“n”个父子关系，但我不明白它是如何工作的以及 lft、right 字段如何包含值。我想深入了解树的行为。

CREATE TABLE categories (
        id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        parent_id INTEGER(10) DEFAULT NULL,
        lft INTEGER(10) DEFAULT NULL,
        rght INTEGER(10) DEFAULT NULL,
        name VARCHAR(255) DEFAULT '',
        PRIMARY KEY  (id)
    );

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES(1, 'My Categories', NULL, 1, 30);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES(2, 'Fun', 1, 2, 15);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES(3, 'Sport', 2, 3, 8);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES(4, 'Surfing', 3, 4, 5);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES(5, 'Extreme knitting', 3, 6, 7);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES(6, 'Friends', 2, 9, 14);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (7, 'Gerald', 6, 10, 11);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (8, 'Gwendolyn', 6, 12, 13);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (9, 'Work', 1, 16, 29);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (10, 'Reports', 9, 17, 22);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (11, 'Annual', 10, 18, 19);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (12, 'Status', 10, 20, 21);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (13, 'Trips', 9, 23, 28);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (14, 'National', 13, 24, 25);

INSERT INTO categories (id, name, parent_id, lft, rght)
VALUES (15, 'International', 13, 26, 27);

Answer 1

我希望下图能帮助您理解 TreeBehavior 的算法。

Cakephp 为 TreeBehavior 使用 MPTT 逻辑。

这存储在如下表中:

如需进一步阅读，请访问以下链接:

https://www.sitepoint.com/hierarchical-data-database-2/