如何将带外键的关联临时交易表数据迁移至关联永久表

需求说明 #

需要将两张带外键关联的临时交易表数据复制后删除原数据，迁移至结构一致的永久交易表中，要求：

• 迁移前必须验证临时表数据是平衡的（避免不平衡数据进入永久表）
• 临时表的主键不转移至永久表
• 避免因数据不平衡导致永久表记录被删除

现有表结构 #

CREATE TABLE `tbl_0_accs_fin_trans` (
  `fin_trans_id` int NOT NULL,
  `fin_trans_tstamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fin_trans_description` text NOT NULL,
  `fin_trans_balance` decimal(20,2) DEFAULT NULL
) ;

CREATE TABLE `tbl_0_accs_fin_trans_journals` (
  `journal_id` int UNSIGNED NOT NULL,
  `fin_trans_id` int NOT NULL,
  `acc_id` int NOT NULL,
  `journal_amount` decimal(20,2) NOT NULL,
  `acc_entry_type_id` int NOT NULL
) ;

CREATE TABLE `tbl_0_accs_fin_trans_tmp` (
  `fin_trans_id` int NOT NULL,
  `fin_trans_tstamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fin_trans_description` text NOT NULL,
  `fin_trans_balance` decimal(20,2) DEFAULT NULL
) ;

CREATE TABLE `tbl_0_accs_fin_trans_journals_tmp` (
  `journal_id` int UNSIGNED NOT NULL,
  `fin_trans_id` int NOT NULL,
  `acc_id` int NOT NULL,
  `journal_amount` decimal(20,2) NOT NULL,
  `acc_entry_type_id` int NOT NULL
) ;

ALTER TABLE `tbl_0_accs_fin_trans`
  ADD PRIMARY KEY (`fin_trans_id`);

ALTER TABLE `tbl_0_accs_fin_trans_journals`
  ADD PRIMARY KEY (`journal_id`),
  ADD UNIQUE KEY `journal_id` (`journal_id`),
  ADD KEY `fkey_fin_trans_id` (`fin_trans_id`),
  ADD KEY `fkey_acc_id` (`acc_id`);

ALTER TABLE `tbl_0_accs_fin_trans_tmp`
  ADD PRIMARY KEY (`fin_trans_id`);

ALTER TABLE `tbl_0_accs_fin_trans_journals_tmp`
  ADD PRIMARY KEY (`journal_id`),
  ADD UNIQUE KEY `journal_id` (`journal_id`),
  ADD KEY `fkey_fin_trans_id_tmp` (`fin_trans_id`),
  ADD KEY `fkey_acc_id_tmp` (`acc_id`);

ALTER TABLE `tbl_0_accs_fin_trans_journals`
  ADD CONSTRAINT `fkey_acc_id` FOREIGN KEY (`acc_id`) REFERENCES `tbl_0_accs` (`acc_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `fkey_fin_trans_id` FOREIGN KEY (`fin_trans_id`) REFERENCES `tbl_0_accs_fin_trans` (`fin_trans_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `tbl_0_accs_fin_trans_journals_tmp`
  ADD CONSTRAINT `fkey_acc_id_tmp` FOREIGN KEY (`acc_id`) REFERENCES `tbl_0_accs` (`acc_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `fkey_fin_trans_id_tmp` FOREIGN KEY (`fin_trans_id`) REFERENCES `tbl_0_accs_fin_trans_tmp` (`fin_trans_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

预期效果 #

现有尝试与求助 #

曾用触发器实现过单表数据迁移，但未找到带外键关联双表场景的相关示例或文档，目前仅创建了临时表，寻求解决思路与示例。

解决思路与示例代码 #

1. 数据平衡性校验 #

先验证临时表中每个交易对应的journal金额总和是否为0（复式记账平衡规则，可根据业务调整），若存在不平衡数据则终止迁移：

-- 查询不平衡的交易
SELECT t.fin_trans_id, SUM(j.journal_amount) AS total_amount
FROM tbl_0_accs_fin_trans_tmp t
JOIN tbl_0_accs_fin_trans_journals_tmp j ON t.fin_trans_id = j.fin_trans_id
GROUP BY t.fin_trans_id
HAVING total_amount != 0;

2. 事务内执行迁移（保证原子性） #

迁移操作必须在事务中执行，确保要么全成功要么全回滚，避免数据不一致：

START TRANSACTION;

-- 校验数据平衡性，存在不平衡数据则回滚
SET @unbalanced_count = (
    SELECT COUNT(*)
    FROM (
        SELECT t.fin_trans_id
        FROM tbl_0_accs_fin_trans_tmp t
        JOIN tbl_0_accs_fin_trans_journals_tmp j ON t.fin_trans_id = j.fin_trans_id
        GROUP BY t.fin_trans_id
        HAVING SUM(j.journal_amount) != 0
    ) AS unbalanced
);

IF @unbalanced_count > 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '存在不平衡的交易数据，终止迁移';
END IF;

-- 创建临时表存储临时ID与永久表新ID的映射
CREATE TEMPORARY TABLE IF NOT EXISTS trans_id_mapping (
    old_fin_trans_id INT,
    new_fin_trans_id INT
);

-- 插入交易主表数据（不使用临时表主键，永久表主键需为自增或生成新值）
INSERT INTO tbl_0_accs_fin_trans (fin_trans_tstamp, fin_trans_description, fin_trans_balance)
SELECT fin_trans_tstamp, fin_trans_description, fin_trans_balance
FROM tbl_0_accs_fin_trans_tmp;

-- 建立临时ID与新ID的映射（通过交易唯一标识关联）
INSERT INTO trans_id_mapping (old_fin_trans_id, new_fin_trans_id)
SELECT t.fin_trans_id, p.fin_trans_id
FROM tbl_0_accs_fin_trans_tmp t
JOIN tbl_0_accs_fin_trans p 
    ON t.fin_trans_tstamp = p.fin_trans_tstamp 
    AND t.fin_trans_description = p.fin_trans_description;

-- 插入journal表数据，替换为永久表的新fin_trans_id
INSERT INTO tbl_0_accs_fin_trans_journals (fin_trans_id, acc_id, journal_amount, acc_entry_type_id)
SELECT m.new_fin_trans_id, j.acc_id, j.journal_amount, j.acc_entry_type_id
FROM tbl_0_accs_fin_trans_journals_tmp j
JOIN trans_id_mapping m ON j.fin_trans_id = m.old_fin_trans_id;

-- 删除临时表中已迁移的数据
DELETE j FROM tbl_0_accs_fin_trans_journals_tmp j
JOIN trans_id_mapping m ON j.fin_trans_id = m.old_fin_trans_id;

DELETE t FROM tbl_0_accs_fin_trans_tmp t
JOIN trans_id_mapping m ON t.fin_trans_id = m.old_fin_trans_id;

-- 提交事务
COMMIT;

-- 清理临时映射表
DROP TEMPORARY TABLE IF EXISTS trans_id_mapping;

3. 关键注意事项 #

• 确保永久表的fin_trans_id、journal_id为自增主键，或有生成新主键的逻辑，避免主键冲突
• 平衡性校验逻辑需匹配实际业务规则
• 若临时表数据量较大，需分批迁移以减少锁表影响
• 迁移前建议备份数据，避免意外情况

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