MySQL主键冲突问题分析处理

背景

因公司业务及预算调整,系统部署从原有云服务提供商迁移到另外一家云服务提供商,在测试新服务能力的时候,发现应用系统某个功能不能正常使用,仅仅是第一次成功。

为了分析问题,笔者使用以下环境还原报错场景进行讲解。

  • Spring Boot: 3.0.2
  • MySQL: 5.7.31
  • MyBatis: 3.5.1

问题分析

通过查看服务日志,发现后端接口报SQL异常-主键冲突,如下图所示:

刚开始看到这个错误信息,直接就懵了,怎么在另外一个服务商那里跑得好好的,到这边就主键冲突了呢。一通百度、Google之后,突然之间有个想法,会不会是这两个云服务商提供的MySQL服务,某些参数有区别。

分析数据

因为数据是迁移过来的,表中有大量旧数据,不好确定到底是那个值冲突了。

分析代码

然后查看我们代码,找到对应的PO类代码,代码类似以下,主要关注主键属性 id 的数据类型。

public class UserPo {
   
   
    private int id;
    private String name;
    private String email;
    private String password;
}

代码中发现id属性是int类型的,我们知道,Java中int类型默认值为0,在新增数据的时候并没有为id属性设置值,代码类似下面这样:

UserPo user = new UserPo(
### MySQL 主键冲突错误处理解决方案 在 MySQL 数据库操作过程中,主键冲突是一个常见的问题。以下是几种有效的解决方案及其适用场景: #### 使用 `REPLACE INTO` 替代原有数据 当遇到主键冲突时,可以使用 `REPLACE INTO` 语句来替代原有的数据记录。该语句会先尝试插入新记录;如果发现主键冲突,则删除旧记录并插入新的记录。 ```sql REPLACE INTO table_name (col1, col2, col3) VALUES ('val1', 'val2', 'val3'); ``` 此方法适用于需要完全替换已有记录的情况,但需注意它可能会导致一些额外的日志开销和性能影响[^3]。 #### 利用 `INSERT IGNORE` 跳过冲突 通过 `INSERT IGNORE` 可以忽略因主键或唯一索引引发的冲突警告而不终止执行流程。这种方式不会覆盖已存在的记录而是简单地跳过它们。 ```sql INSERT IGNORE INTO table_name (id, name) VALUES (1, 'John Doe'), (2, 'Jane Smith'); ``` 这种方法适合于那些希望保留现有数据且不需要更新任何字段的应用场合[^1]. #### 应用 `ON DUPLICATE KEY UPDATE` 更为灵活的方式是采用带有条件逻辑的 `ON DUPLICATE KEY UPDATE` 子句,在检测到主键重复的情况下自动触发指定列值的变化而非抛出异常停止程序运行。 ```sql INSERT INTO table_name (id, value_column) VALUES (unique_id_value, new_data_value) ON DUPLICATE KEY UPDATE value_column = VALUES(value_column); ``` 上述例子展示了如何仅修改特定列的内容而不是整个行被取代的情形下解决主键碰撞的问题. #### 防范措施与后续维护建议 除了以上提到的技术手段外,还应该考虑长期的数据一致性和健壮性的保障机制。例如定期利用工具如 pt-table-checksum 和 pt-table-sync 来验证主从数据库间的一致性状态,并及时修复可能产生的差异之处。 另外一种预防策略是在批量导入前预先创建一个辅助序列号生成器表用于分配独一无二的新ID给即将加入的目标集合成员们,从而有效规避潜在的PK重叠风险[^2]: ```sql -- 创建临时自增表 CREATE TEMPORARY TABLE temp_seq ( seq INT AUTO_INCREMENT PRIMARY KEY, dummy CHAR(1) ); -- 插入足够的占位符以便获取连续编号 INSERT INTO temp_seq(dummy) SELECT NULL FROM source_table; -- 执行安全无冲撞的大规模迁移动作 INSERT INTO target_table(id, other_columns...) SELECT CONCAT('prefix_', ts.seq), st.other_columns... FROM source_table AS st JOIN temp_seq AS ts; ``` 这种技巧特别有助于复杂环境下保持事务隔离级别的同时完成高效稳定的ETL作业过程. ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值