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', CONCAT('1.', 2)),
(3, 'Laptops', CONCAT('1.2.', 3)),
(4, 'Desktops', CONCAT('1.2.', 4));

-- 查询某个节点的所有子节点
SELECT * FROM categories WHERE path LIKE CONCAT('1.2.%', '');

-- 创建主表
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 创建闭包表
CREATE TABLE category_paths (
    ancestor_id INT,
    descendant_id INT,
    depth INT,
    PRIMARY KEY (ancestor_id, descendant_id)
);

-- 插入数据
INSERT INTO categories (id, name) VALUES
(1, 'Electronics'),
(2, 'Computers'),
(3, 'Laptops'),
(4, 'Desktops');

INSERT INTO category_paths (ancestor_id, descendant_id, depth) VALUES
(1, 1, 0),
(1, 2, 1),
(1, 3, 2),
(1, 4, 2),
(2, 2, 0),
(2, 3, 1),
(2, 4, 1),
(3, 3, 0),
(4, 4, 0);

-- 查询某个节点的所有子节点
SELECT c.* FROM categories c
JOIN category_paths cp ON c.id = cp.descendant_id
WHERE cp.ancestor_id = 2;