mysql 树状数据库

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    path VARCHAR(255)
);

INSERT INTO categories (id, name, path) VALUES
(1, 'Electronics', '1'),
(2, 'Computers', '1,2'),
(3, 'Laptops', '1,2,3'),
(4, 'Accessories', '1,4');

SELECT * FROM categories WHERE path LIKE '1,2,%';

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE category_closure (
    ancestor_id INT,
    descendant_id INT,
    depth INT,
    PRIMARY KEY (ancestor_id, descendant_id),
    FOREIGN KEY (ancestor_id) REFERENCES categories(id),
    FOREIGN KEY (descendant_id) REFERENCES categories(id)
);

INSERT INTO categories (id, name) VALUES (1, 'Electronics');
INSERT INTO categories (id, name) VALUES (2, 'Computers');
INSERT INTO category_closure (ancestor_id, descendant_id, depth) VALUES
(1, 1, 0),
(1, 2, 1),
(2, 2, 0),
(2, 3, 1);

SELECT c.* FROM categories c
JOIN category_closure cc ON c.id = cc.descendant_id
WHERE cc.ancestor_id = 1;