如何用PHP在同结构数据库间迁移关联表数据？含归档需求

背景信息 #

• 现有users和productions两个数据库，核心维护对象为DB_production
• 需要创建仅复制表结构的归档库DB_archive，用于迁移DB_production中的非当前记录，同时支持数据随时迁回主库
• 本地环境：10.1.48-MariaDB-0+deb9u2，存储引擎InnoDB，字符集utf8mb4，排序规则utf8mb4_unicode_520_ci

当前单表迁移方法 #

单表迁移可通过以下SQL实现，反向执行即可完成数据迁回：

INSERT INTO DB_archive.table1 
SELECT * FROM DB_production.table1 WHERE id_table1 = 10;

执行完成后清理主库对应数据：

DELETE FROM DB_production.table1 WHERE id_table1 = 10;

现存疑问与场景需求 #

疑问 #

1. 迁移与table1存在一对一、一对多关联的15张表数据时，是否可以沿用上述单表方法？操作过程中是否需要锁定数据库直到所有关联表迁移完成？
2. 针对该场景，是否有更高效的解决方案？

迁移场景 #

• 支持用户随时手动触发迁移操作
• 通过定时任务（cron job）在非工作时间自动迁移3个月以上的旧数据
• 曾尝试表分区方案，但效果不理想

问题解答 #

1. 关联表迁移与锁库问题 #

• 关联表能否沿用单表方法？
  可以，但需严格按照关联关系顺序执行：先迁移主表（如table1）中需归档的记录，再迁移依赖它的子表数据，避免外键约束报错。反向迁回时则需先迁子表，再迁主表。
• 是否需要锁库？
  不需要全库锁定，但建议在迁移过程中针对涉及的表使用行级锁（InnoDB默认支持），或在事务中执行单组主表+关联表的迁移操作，避免迁移期间数据被修改导致不一致。示例事务操作：

START TRANSACTION;
-- 迁移主表数据
INSERT INTO DB_archive.table1 SELECT * FROM DB_production.table1 WHERE id_table1 = 10;
-- 迁移关联子表数据（示例为一对多关联）
INSERT INTO DB_archive.table_child SELECT * FROM DB_production.table_child WHERE fk_table1 = 10;
-- 删除主库对应数据
DELETE FROM DB_production.table_child WHERE fk_table1 = 10;
DELETE FROM DB_production.table1 WHERE id_table1 = 10;
COMMIT;

批量迁移旧数据时，建议分批次执行，避免单事务过大影响数据库性能。

2. 更优解决方案 #

方案一：mysqldump分批导出导入（适合定时批量迁移）

针对非工作时间的批量旧数据迁移，可通过mysqldump按条件导出数据后导入归档库，最后清理主库数据：

# 导出主表及关联表的3个月以上旧数据（假设table1有create_time字段）
mysqldump -u username -p DB_production table1 table_child1 table_child2 --where="create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH)" --single-transaction > old_data.sql

# 导入归档库
mysql -u username -p DB_archive < old_data.sql

# 验证导入成功后，删除主库旧数据
mysql -u username -p DB_production -e "DELETE FROM table_child1 WHERE fk_table1 IN (SELECT id_table1 FROM table1 WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH)); DELETE FROM table1 WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);"

--single-transaction参数可保证InnoDB引擎下导出数据的一致性，无需锁表。

方案二：封装迁移脚本（支持手动/定时触发）

用Python或Shell编写脚本，封装主表+关联表的迁移逻辑，支持传入参数（如记录ID、时间范围）：

• 手动迁移时，用户输入归档条件，脚本自动按关联顺序执行迁移+删除
• 定时任务时，脚本自动筛选3个月以上旧数据，分批次执行操作

脚本核心逻辑示例（伪代码）：

# 1. 建立数据库连接
# 2. 根据条件获取需归档的主表记录ID列表
# 3. 遍历每个ID：
#    a. 开启事务
#    b. 将主表数据插入归档库
#    c. 将所有关联子表数据插入归档库
#    d. 删除主库子表对应数据
#    e. 删除主库主表对应数据
#    f. 提交事务并记录迁移日志

该方式灵活性高，可适配两种迁移场景，还能加入错误重试、日志记录等机制。

方案三：使用归档存储引擎（Archive Engine）

如果归档数据仅用于查询、无需频繁修改，可将DB_archive中的表转换为ARCHIVE存储引擎，它会自动压缩数据以节省存储空间，且插入性能较好。注意：ARCHIVE引擎不支持事务和外键，需先确保迁移数据的完整性，再转换引擎。

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