与生产库同步的staging数据库插入测试数据出现ID冲突如何解决

短周期快速修复方案 #

无需修改现有业务逻辑，调整同步规则即可解决问题：

• 替换直接INSERT操作为UPSERT逻辑：冲突时优先保留生产端数据。不同数据库对应语法如下：
  • MySQL：INSERT INTO 表名 (字段列表) VALUES (值列表) ON DUPLICATE KEY UPDATE 字段1=VALUES(字段1), 字段2=VALUES(字段2)...
  • PostgreSQL：INSERT INTO 表名 (字段列表) VALUES (值列表) ON CONFLICT (主键字段) DO UPDATE SET 字段1=EXCLUDED.字段1, 字段2=EXCLUDED.字段2...
    若需要保留Staging端的测试数据，可在冲突触发时先将Staging端的冲突记录插入备份表后再执行覆盖操作。
• 同步完成后重置Staging自增ID起始值：每次同步任务结束后，查询生产库对应表的最大ID值，将Staging库表的自增ID起始值设置为大于生产最大ID的高位区间，比如生产当前最大ID为1500，可将Staging的自增起始值设为100000，确保后续测试生成的ID永远不会和生产新增的ID重合。MySQL对应操作命令为ALTER TABLE 表名 AUTO_INCREMENT = 100000;

长期稳定解决方案 #

适合测试数据需要长期保留、不希望被同步操作覆盖的场景：

• 跨环境ID段隔离：提前给不同环境分配独立的ID使用区间，比如生产ID范围为1_{10^8，Staging测试用ID范围为10^8+1}2*10^8，业务层插入测试数据时从预留区间生成ID，不会占用生产的ID资源。
• 替换自增主键为分布式ID：改用UUID、雪花ID这类全局唯一的ID作为主键，天然不存在跨环境ID重复问题，同步时无需额外处理冲突。
• 新增联合主键隔离数据：在Staging库的表中新增env环境标识字段，生产同步过来的记录赋值为prod，测试插入的记录赋值为staging，将原单ID主键调整为id + env联合主键，同ID的生产、测试数据可同时存在互不干扰。查询生产全量数据时仅需增加过滤条件env = 'prod'即可。

同步工具配置优化 #

如果使用第三方同步工具，可以直接开启工具内置的冲突处理规则：

• 开启生产端优先的冲突覆盖策略，冲突发生时默认用生产端的最新数据覆盖Staging端的冲突数据
• 配置自定义冲突回调逻辑，冲突时自动执行测试数据备份、数据合并等自定义操作

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