树结构 mysql

WITH RECURSIVE cte_tree AS (
    SELECT id, parent_id, name
    FROM your_table
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.parent_id, t.name
    FROM your_table t
    INNER JOIN cte_tree ct ON t.parent_id = ct.id
)
SELECT * FROM cte_tree;

INSERT INTO your_table (id, parent_id, name) VALUES (new_id, parent_id, 'new_node_name');

UPDATE your_table SET parent_id = new_parent_id WHERE id = node_id;

-- 级联删除
DELETE FROM your_table WHERE id = node_id OR parent_id = node_id;

-- 保留子节点
DELETE FROM your_table WHERE id = node_id;
UPDATE your_table SET parent_id = new_parent_id WHERE parent_id = node_id;