如何在规范生产数据库中整合混乱外部数据并维护外键关联？

这确实是整合非规范异构数据库时非常头疼的常见问题，我来分享几个经过项目验证的方案，帮你搞定每日同步的需求：

方案一：生成自定义伪主键，基于业务唯一标识做增量同步 #

既然源库没有主键，我们可以自己构造一个能唯一标识源数据记录的伪主键——从源数据中挑选几个业务上不会重复的字段组合（比如用户的手机号+订单编号+下单时间，或者商品的名称+供应商编码+规格），把它们拼接成字符串，或者用哈希函数（比如SHA256）生成一个固定长度的唯一值，作为伪主键存入你的目标表。

具体操作流程：

• 首次全量导入：给每条源数据生成伪主键，插入目标表。
• 每日同步：
  1. 先给当日的源数据生成同样规则的伪主键。
  2. 和目标表的伪主键做匹配：
     • 匹配成功：对比其他字段的值，如果有变化就执行UPDATE（避免无意义的更新操作）。
     • 匹配失败：执行INSERT，把新记录加入目标表。
     • 目标表有但源数据没有的伪主键：根据业务需求选择软删除（标记is_deleted=1）或者硬删除（如果外键允许的话，优先软删除）。

⚠️ 注意：一定要确保你选的组合字段在源数据里真的能唯一标识记录，如果源数据本身就有重复的组合，那可以再加一个字段（比如最后修改时间），或者对重复的记录追加序号来区分。

方案二：用变更数据捕获（CDC）跳过全量同步 #

如果源数据库支持CDC（比如MySQL的binlog、PostgreSQL的WAL日志、SQL Server的CDC功能），那直接监听源库的变更日志会更高效：

• 不需要依赖源数据的主键，直接捕获源库的新增、修改、删除操作，同步到你的目标表。
• 这种方式是增量同步，比每日全量拉取要快很多，也能减少数据不一致的风险。

如果源库是比较老旧的类型（比如Access、Excel文件），可以用ETL工具模拟CDC的效果——这些工具能帮你对比前后两次的源数据差异，自动识别新增、修改的记录。

方案三：中间过渡表+MERGE合并策略 #

这个方案能完美避开truncate破坏外键的问题：

1. 每日先把源数据全量导入到一个无外键的临时过渡表——这个表可以放心truncate，因为它不关联任何其他表。
2. 然后用数据库的MERGE语句（MySQL 8.0+、SQL Server、Oracle都支持），基于伪主键把过渡表的数据合并到目标表：

   MERGE INTO your_target_table t
   USING temp_staging_table s
   ON t.pseudo_id = s.pseudo_id
   WHEN MATCHED THEN UPDATE SET t.field1 = s.field1, t.field2 = s.field2
   WHEN NOT MATCHED THEN INSERT (pseudo_id, field1, field2) VALUES (s.pseudo_id, s.field1, s.field2)
   WHEN NOT MATCHED BY SOURCE THEN UPDATE SET t.is_deleted = 1;
   

3. 如果你的数据库不支持MERGE，可以拆成三个步骤：先更新匹配的记录，再插入不匹配的，最后处理需要删除的记录。

额外实践建议 #

• 优先软删除：不要直接删除目标表的记录，加一个is_deleted字段标记删除，这样既不破坏外键引用，也能在同步出错时恢复数据。
• 加数据校验：同步完成后，统计源数据和目标表的记录数，或者抽样检查关键字段，确保数据一致。
• 记录同步日志：把每日同步的操作（新增/更新/删除的条数）和错误信息记录下来，方便排查问题。
• 推动源库规范：如果有可能，和对方应用团队沟通，慢慢推动他们给源数据库加上主键或唯一约束——这才是解决问题的根本办法，长期来看能省很多事。

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