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');

-- 查询节点3的所有祖先
SELECT * FROM categories WHERE path LIKE '1.2.%';

CREATE TABLE category_closure (
    ancestor_id INT,
    descendant_id INT,
    depth INT,
    PRIMARY KEY (ancestor_id, descendant_id)
);

-- 插入示例数据
INSERT INTO category_closure (ancestor_id, descendant_id, depth) VALUES
(1, 1, 0),
(1, 2, 1),
(1, 3, 2),
(1, 4, 1),
(2, 2, 0),
(2, 3, 1),
(4, 4, 0);

-- 查询节点2的所有子孙
SELECT c.* FROM categories c
JOIN category_closure cc ON c.id = cc.descendant_id
WHERE cc.ancestor_id = 2;

-- 假设我们要在节点2下插入一个新节点'Desktops'
SET @last_id = (SELECT MAX(id) FROM categories) + 1;
SET @new_path = (SELECT path FROM categories WHERE id = 2) . '.@last_id';
INSERT INTO categories (id, name, path) VALUES (@last_id, 'Desktops', @new_path);

-- 假设我们要删除节点3
DELETE FROM categories WHERE id = 3;
UPDATE categories SET path = REPLACE(path, '1.2.3', '1.2') WHERE path LIKE '1.2.3%';