MySQL关联表批量复制问询：Accounts与Holdings表复制难题

针对你遇到的Holdings表因关联Accounts自增id_acc无法直接复制的问题，以下是几种实用的解决方案：

方案一：用临时表存储新旧Accounts ID映射 #

这个方案核心是先记录复制Accounts时生成的新id_acc与原id_acc的对应关系，再通过映射复制Holdings数据。

1. 创建临时表存储映射关系

   CREATE TEMPORARY TABLE temp_acc_map (
       old_id_acc INT,
       new_id_acc INT
   );
   

2. 复制Accounts数据并生成映射
   若使用MySQL 8.0及以上版本，支持INSERT...RETURNING语法，可通过Accounts的唯一业务字段（如account_code）精准关联新旧记录：

   INSERT INTO temp_acc_map(old_id_acc, new_id_acc)
   SELECT old.id_acc, new.id_acc
   FROM Accounts old
   JOIN (
       INSERT INTO Accounts(id_cc, account_code, 其他字段) -- 替换为Accounts实际字段
       SELECT 114, account_code, 其他字段
       FROM Accounts
       WHERE id_cc = 原id_cc -- 替换为目标原Color Chart ID
       RETURNING id_acc, account_code
   ) new ON old.account_code = new.account_code;
   

   若MySQL版本较低不支持RETURNING，可通过事务结合自增ID范围生成映射：

   START TRANSACTION;
   -- 获取Accounts表当前自增起始值
   SELECT AUTO_INCREMENT INTO @start_id
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = '你的数据库名' AND TABLE_NAME = 'Accounts';
   
   -- 插入Accounts数据
   INSERT INTO Accounts(id_cc, 其他字段)
   SELECT 114, 其他字段
   FROM Accounts
   WHERE id_cc = 原id_cc;
   
   -- 获取插入的行数
   SELECT ROW_COUNT() INTO @row_count;
   
   -- 生成新旧ID映射（需保证原Accounts的id_acc按顺序排列）
   INSERT INTO temp_acc_map(old_id_acc, new_id_acc)
   SELECT old.id_acc, @start_id + (ROW_NUMBER() OVER (ORDER BY old.id_acc) - 1)
   FROM Accounts old
   WHERE old.id_cc = 原id_cc
   ORDER BY old.id_acc;
   COMMIT;
   

3. 通过映射复制Holdings数据

   INSERT INTO Holdings(id_acc, id_cc, type, name, amount)
   SELECT map.new_id_acc, 114, h.type, h.name, h.amount
   FROM Holdings h
   JOIN temp_acc_map map ON h.id_acc = map.old_id_acc
   WHERE h.id_cc = 原id_cc;
   

4. 清理临时表

   DROP TEMPORARY TABLE IF EXISTS temp_acc_map;
   

方案二：封装存储过程一键执行 #

把整个复制逻辑封装成存储过程，方便后续重复调用：

DELIMITER //
CREATE PROCEDURE CopyColorChartData(IN original_cc_id INT, IN new_cc_id INT)
BEGIN
    DECLARE start_id INT;
    DECLARE row_count INT;

    -- 创建临时映射表
    CREATE TEMPORARY TABLE temp_acc_map (
        old_id_acc INT,
        new_id_acc INT
    );

    START TRANSACTION;

    -- 获取Accounts自增起始值
    SELECT AUTO_INCREMENT INTO start_id
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Accounts';

    -- 复制Accounts数据
    INSERT INTO Accounts(id_cc, 其他字段) -- 替换为实际字段
    SELECT new_cc_id, 其他字段
    FROM Accounts
    WHERE id_cc = original_cc_id;

    SELECT ROW_COUNT() INTO row_count;

    -- 生成ID映射
    INSERT INTO temp_acc_map(old_id_acc, new_id_acc)
    SELECT old.id_acc, start_id + (ROW_NUMBER() OVER (ORDER BY old.id_acc) - 1)
    FROM Accounts old
    WHERE old.id_cc = original_cc_id
    ORDER BY old.id_acc;

    -- 复制Holdings数据
    INSERT INTO Holdings(id_acc, id_cc, type, name, amount)
    SELECT map.new_id_acc, new_cc_id, h.type, h.name, h.amount
    FROM Holdings h
    JOIN temp_acc_map map ON h.id_acc = map.old_id_acc
    WHERE h.id_cc = original_cc_id;

    COMMIT;
    DROP TEMPORARY TABLE IF EXISTS temp_acc_map;
END //
DELIMITER ;

调用时只需执行：

CALL CopyColorChartData(原id_cc, 114);

方案三：利用Accounts的唯一业务字段直接关联 #

如果Accounts表有唯一的业务标识（如account_number），可跳过临时表，直接通过该字段关联新旧Accounts记录：

-- 先复制Accounts数据
INSERT INTO Accounts(id_cc, account_number, 其他字段)
SELECT 114, account_number, 其他字段
FROM Accounts
WHERE id_cc = 原id_cc;

-- 再复制Holdings数据
INSERT INTO Holdings(id_acc, id_cc, type, name, amount)
SELECT new.id_acc, 114, h.type, h.name, h.amount
FROM Holdings h
JOIN Accounts old ON h.id_acc = old.id_acc
JOIN Accounts new ON old.account_number = new.account_number AND new.id_cc = 114
WHERE h.id_cc = 原id_cc;

注意事项 #

• 所有复制操作建议在事务中执行，避免部分失败导致数据不一致
• 优先使用MySQL 8.0+的INSERT...RETURNING语法，映射关系更可靠
• 临时表是会话级别的，会话结束后会自动销毁，无需担心残留数据

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