MySQL 复制层级表树结构并更新关联ID及environment_id方案咨询

核心问题 #

你原有脚本依赖text字段匹配新旧记录的逻辑不稳定，一旦同环境下存在重复text就会关联错误，且子查询未限定源数据范围，容易拉取错误的关联ID。

实现思路 #

通过临时表存储每个层级旧主键-新主键的映射关系，基于映射关系插入子表数据，保证关联正确性。

步骤1：插入table_a并生成新旧ID映射 #

-- 新建临时表存储table_a新旧主键映射
CREATE TEMP TABLE map_a (
    old_formulaid INT,
    new_formulaid INT
);

-- 插入table_a目标环境数据
INSERT INTO table_a (environment_id, text, is_default, type)
SELECT -1001, text, is_default, type
FROM table_a
WHERE environment_id = -1005;

-- 填充table_a映射关系，可增加is_default、type等字段保证匹配唯一性
INSERT INTO map_a (old_formulaid, new_formulaid)
SELECT old_a.formulaid, new_a.formulaid
FROM table_a old_a
INNER JOIN table_a new_a 
    ON old_a.text = new_a.text
    AND old_a.environment_id = -1005
    AND new_a.environment_id = -1001;

步骤2：插入table_b并生成新旧ID映射 #

-- 新建临时表存储table_b新旧主键映射
CREATE TEMP TABLE map_b (
    old_id INT,
    new_id INT
);

-- 插入table_b目标环境数据，关联map_a获取新的a_id
INSERT INTO table_b (environment_id, a_id, ordinal, text, level)
SELECT 
    -1001,
    map_a.new_formulaid,
    old_b.ordinal,
    old_b.text,
    old_b.level
FROM table_b old_b
INNER JOIN map_a ON old_b.a_id = map_a.old_formulaid
WHERE old_b.environment_id = -1005;

-- 填充table_b映射关系，可增加ordinal、level等字段保证匹配唯一性
INSERT INTO map_b (old_id, new_id)
SELECT old_b.id, new_b.id
FROM table_b old_b
INNER JOIN table_b new_b
    ON old_b.text = new_b.text
    AND old_b.ordinal = new_b.ordinal
    AND old_b.level = new_b.level
    AND old_b.environment_id = -1005
    AND new_b.environment_id = -1001;

步骤3：插入table_c数据 #

-- 插入table_c目标环境数据，关联map_b获取新的b_id
INSERT INTO table_c (environment_id, b_id, languageid, translation)
SELECT
    -1001,
    map_b.new_id,
    old_c.languageid,
    old_c.translation
FROM table_c old_c
INNER JOIN map_b ON old_c.b_id = map_b.old_id
WHERE old_c.environment_id = -1005;

注意事项 #

1. 匹配新旧记录时如果text可能重复，可加入更多业务唯一字段共同作为关联条件，避免映射错误
2. 如果你使用PostgreSQL、SQL Server等支持RETURNING/OUTPUT子句的数据库，可以在插入数据时直接返回新旧ID，无需后续关联填充映射表，效率和准确性更高
3. 正式执行前可先将INSERT语句替换为SELECT，验证返回的数据和关联ID是否符合预期，确认无误后再执行插入操作

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