MySQL基于Adjacency List模型实现节点及其子节点的复制

需求说明 #

复制指定节点及其所有下属子节点，以Site 1节点（原id=2）为例，要求复制后新生成的id、parent_id均为唯一值，节点title保持与原节点一致。

实现方案 #

方案1：MySQL 8.0及以上版本（支持递归CTE） #

核心思路是通过递归CTE拉取所有待复制的节点树，借助临时表存储新旧id的映射关系，按层级顺序插入节点保证父节点新id可被后续子节点引用。

-- 1. 创建临时表存储新旧id映射
CREATE TEMPORARY TABLE IF NOT EXISTS id_mapping (
    old_id INT UNSIGNED PRIMARY KEY,
    new_id INT UNSIGNED NOT NULL
);

-- 2. 先插入待复制的根节点，记录映射
SET @source_root_id = 2; -- 要复制的源根节点id
INSERT INTO node_structure_data (title, parent_id)
SELECT title, parent_id FROM node_structure_data WHERE id = @source_root_id;
INSERT INTO id_mapping (old_id, new_id) VALUES (@source_root_id, 2459539);

-- 3. 递归拉取所有子节点，按层级插入并更新映射
WITH RECURSIVE child_nodes AS (
    -- 第一层子节点
    SELECT id, title, parent_id FROM node_structure_data WHERE parent_id = @source_root_id
    UNION ALL
    -- 递归获取所有深层子节点
    SELECT c.id, c.title, c.parent_id FROM node_structure_data c
    JOIN child_nodes p ON c.parent_id = p.id
)
INSERT INTO node_structure_data (title, parent_id)
SELECT 
    cn.title,
    m.new_id
FROM child_nodes cn
JOIN id_mapping m ON cn.parent_id = m.old_id;

-- 4. 清理临时表（可选，会话结束会自动销毁）
DROP TEMPORARY TABLE IF EXISTS id_mapping;

方案2：MySQL 5.x 低版本兼容方案 #

如果不支持递归CTE，可以通过分层迭代插入实现，层级固定的场景可以直接逐层编写SQL，层级不固定的场景可以封装为存储过程循环处理：

-- 1. 创建临时映射表
CREATE TEMPORARY TABLE IF NOT EXISTS id_mapping (
    old_id INT UNSIGNED PRIMARY KEY,
    new_id INT UNSIGNED NOT NULL
);

-- 2. 插入根节点
SET @old_root = 2;
INSERT INTO node_structure_data (title, parent_id)
SELECT title, parent_id FROM node_structure_data WHERE id = @old_root;
SET @new_root = 2459539;
INSERT INTO id_mapping (old_id, new_id) VALUES (@old_root, @new_root);

-- 3. 插入第一层子节点（原parent_id=2）
INSERT INTO node_structure_data (title, parent_id)
SELECT title, @new_root FROM node_structure_data WHERE parent_id = @old_root;
-- 记录该层映射
SET @row_num = 0;
SET @last_id = 2459539;
INSERT INTO id_mapping (old_id, new_id)
SELECT id, @last_id + (@row_num := @row_num +1) -1 
FROM node_structure_data WHERE parent_id = @old_root;

-- 4. 插入第二层子节点（原parent_id=3）
INSERT INTO node_structure_data (title, parent_id)
SELECT c.title, m.new_id 
FROM node_structure_data c
JOIN id_mapping m ON c.parent_id = m.old_id
WHERE c.parent_id = 3;

-- 5. 更多层级依此类推，层级不确定时可以写存储过程循环判断是否还有未处理的子节点

验证结果 #

执行完成后查询node_structure_data表，即可得到预期的复制后数据：

id  title   parent_id
1   Division  NULL
2   Site 1    1
3   Paper     2
4   ms1       3
5   Site 1    1
6   Paper     5
7   ms1       6

内容的提问来源于stack exchange，提问作者Herker