如何编写SQL脚本克隆带关联关系的记录并更新关联字段？

针对你需要克隆带有交叉关联（Teachers关联Students/其他Teachers，Students关联Teachers/其他Students）的记录并更新关联字段的需求，以下是几种可行的实现方案：

方案一：临时表存储ID映射关系 #

这是通用度最高的方案，几乎适用于所有关系型数据库，核心思路是先记录原记录ID和新克隆记录ID的映射关系，再通过映射表更新关联字段。

步骤： #

1. 创建临时表，用于存储原ID与新克隆ID的映射，区分表类型：

CREATE TEMPORARY TABLE id_mapping (
    original_id INT,
    new_id INT,
    record_type VARCHAR(20) -- 标记是Teacher还是Student
);

2. 插入克隆的Teacher记录，同时将原ID和新ID存入映射表：

-- 插入Teacher克隆记录，这里以PostgreSQL为例，其他数据库需调整ID捕获逻辑
INSERT INTO Teachers (teacher_id, student_id, [其他业务字段])
SELECT teacher_id, student_id, [其他业务字段] -- 排除自增ID列
FROM Teachers
WHERE id IN (100,101) -- 指定要克隆的原记录ID
RETURNING id AS new_id, original_id INTO id_mapping;

-- 若数据库不支持RETURNING，可先插入克隆记录，再通过唯一业务字段匹配原记录与克隆记录，将ID对存入映射表

3. 同理插入克隆的Student记录，并存入映射表：

INSERT INTO Students (teacher_id, student_id, [其他业务字段])
SELECT teacher_id, student_id, [其他业务字段]
FROM Students
WHERE id IN (100,101)
RETURNING id AS new_id, original_id INTO id_mapping;

4. 更新克隆Teacher的关联字段，替换原ID为对应新ID：

UPDATE Teachers t
SET 
    teacher_id = (SELECT new_id FROM id_mapping WHERE original_id = t.teacher_id AND record_type = 'Teacher'),
    student_id = (SELECT new_id FROM id_mapping WHERE original_id = t.student_id AND record_type = 'Student')
WHERE t.id IN (SELECT new_id FROM id_mapping WHERE record_type = 'Teacher');

5. 更新克隆Student的关联字段：

UPDATE Students s
SET 
    teacher_id = (SELECT new_id FROM id_mapping WHERE original_id = s.teacher_id AND record_type = 'Teacher'),
    student_id = (SELECT new_id FROM id_mapping WHERE original_id = s.student_id AND record_type = 'Student')
WHERE s.id IN (SELECT new_id FROM id_mapping WHERE record_type = 'Student');

方案二：使用CTE批量插入并维护映射（适用于PostgreSQL/SQL Server等支持CTE的数据库） #

利用CTE的特性，在插入时直接生成映射关系，一步完成插入和关联更新：

WITH cloned_teachers AS (
    INSERT INTO Teachers (teacher_id, student_id, [其他业务字段])
    SELECT teacher_id, student_id, [其他业务字段]
    FROM Teachers
    WHERE id IN (100,101)
    RETURNING id AS new_id, original_id
),
cloned_students AS (
    INSERT INTO Students (teacher_id, student_id, [其他业务字段])
    SELECT teacher_id, student_id, [其他业务字段]
    FROM Students
    WHERE id IN (100,101)
    RETURNING id AS new_id, original_id
)
-- 更新Teacher关联
UPDATE Teachers t
SET teacher_id = ct.new_id
FROM cloned_teachers ct
WHERE t.teacher_id = ct.original_id AND t.id IN (SELECT new_id FROM cloned_teachers);

UPDATE Teachers t
SET student_id = cs.new_id
FROM cloned_students cs
WHERE t.student_id = cs.original_id AND t.id IN (SELECT new_id FROM cloned_teachers);

-- 更新Student关联
UPDATE Students s
SET teacher_id = ct.new_id
FROM cloned_teachers ct
WHERE s.teacher_id = ct.original_id AND s.id IN (SELECT new_id FROM cloned_students);

UPDATE Students s
SET student_id = cs.new_id
FROM cloned_students cs
WHERE s.student_id = cs.original_id AND s.id IN (SELECT new_id FROM cloned_students);

方案三：编写存储过程封装克隆逻辑 #

如果需要频繁执行克隆操作，可将上述逻辑封装为存储过程，方便调用：

-- MySQL存储过程示例
DELIMITER //
CREATE PROCEDURE CloneAssociatedRecords()
BEGIN
    -- 创建临时映射表
    CREATE TEMPORARY TABLE id_mapping (
        original_id INT,
        new_id INT,
        record_type VARCHAR(20)
    );

    -- 克隆Teacher并记录映射
    INSERT INTO Teachers (teacher_id, student_id, name) -- 替换为实际业务字段
    SELECT teacher_id, student_id, CONCAT(name, '-CLONE')
    FROM Teachers
    WHERE id IN (100,101);
    
    -- 通过名称匹配原记录与克隆记录，存入映射
    INSERT INTO id_mapping (original_id, new_id, record_type)
    SELECT t1.id AS original_id, t2.id AS new_id, 'Teacher'
    FROM Teachers t1
    JOIN Teachers t2 ON t2.name = CONCAT(t1.name, '-CLONE')
    WHERE t1.id IN (100,101);

    -- 克隆Student并记录映射
    INSERT INTO Students (teacher_id, student_id, name)
    SELECT teacher_id, student_id, CONCAT(name, '-CLONE')
    FROM Students
    WHERE id IN (100,101);
    
    INSERT INTO id_mapping (original_id, new_id, record_type)
    SELECT s1.id AS original_id, s2.id AS new_id, 'Student'
    FROM Students s1
    JOIN Students s2 ON s2.name = CONCAT(s1.name, '-CLONE')
    WHERE s1.id IN (100,101);

    -- 更新Teacher关联字段
    UPDATE Teachers t
    SET 
        teacher_id = (SELECT new_id FROM id_mapping WHERE original_id = t.teacher_id AND record_type = 'Teacher'),
        student_id = (SELECT new_id FROM id_mapping WHERE original_id = t.student_id AND record_type = 'Student')
    WHERE t.id IN (SELECT new_id FROM id_mapping WHERE record_type = 'Teacher');

    -- 更新Student关联字段
    UPDATE Students s
    SET 
        teacher_id = (SELECT new_id FROM id_mapping WHERE original_id = s.teacher_id AND record_type = 'Teacher'),
        student_id = (SELECT new_id FROM id_mapping WHERE original_id = s.student_id AND record_type = 'Student')
    WHERE s.id IN (SELECT new_id FROM id_mapping WHERE record_type = 'Student');

    -- 清理临时表
    DROP TEMPORARY TABLE id_mapping;
END //
DELIMITER ;

-- 调用存储过程
CALL CloneAssociatedRecords();

关键注意事项： #

• 克隆记录时需通过唯一业务标识（如名称后缀、自定义标记）匹配原记录与克隆记录，避免ID映射错误。
• 操作前开启事务，若克隆或更新失败可回滚，防止数据不一致。
• 不同数据库的ID捕获语法有差异，需根据使用的数据库调整（如MySQL用LAST_INSERT_ID配合批量插入处理，SQL Server用OUTPUT子句）。

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