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 '1.%';

DELIMITER //

CREATE PROCEDURE InsertCategory(IN p_name VARCHAR(255), IN p_parent_id INT)
BEGIN
    DECLARE v_path VARCHAR(255);
    SELECT path INTO v_path FROM categories WHERE id = p_parent_id;
    IF v_path IS NULL THEN
        SET v_path = CAST(p_parent_id AS CHAR);
    ELSE
        SET v_path = CONCAT(v_path, '.', p_parent_id);
    END IF;
    INSERT INTO categories (id, name, path) VALUES (LAST_INSERT_ID(), p_name, v_path);
END //

DELIMITER ;