mysql组织机构树状

-- 邻接列表模型示例
CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES organization(id)
);

-- 路径枚举模型示例
CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    path VARCHAR(255)
);

-- 嵌套集模型示例
CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    lft INT,
    rgt INT
);

-- 插入节点示例（邻接列表模型）
DELIMITER //
CREATE PROCEDURE insert_node(IN p_name VARCHAR(255), IN p_parent_id INT)
BEGIN
    DECLARE new_id INT;
    INSERT INTO organization (name, parent_id) VALUES (p_name, p_parent_id);
    SET new_id = LAST_INSERT_ID();
    UPDATE organization SET parent_id = new_id WHERE id = p_parent_id;
END //
DELIMITER ;

-- 删除节点示例（嵌套集模型）
DELIMITER //
CREATE PROCEDURE delete_node(IN p_id INT)
BEGIN
    DECLARE lft_val INT;
    DECLARE rgt_val INT;
    SELECT lft, rgt INTO lft_val, rgt_val FROM organization WHERE id = p_id;
    DELETE FROM organization WHERE lft BETWEEN lft_val AND rgt_val;
    UPDATE organization SET lft = lft - (rgt_val - lft_val + 1) WHERE lft > rgt_val;
    UPDATE organization SET rgt = rgt - (rgt_val - lft_val + 1) WHERE rgt > rgt_val;
    DELETE FROM organization WHERE id = p_id;
END //
DELIMITER ;