文章目录每日一句正能量1. 背景与问题ON DUPLICATE KEY迁移的不是SQL语法而是“重复请求怎么处理”的协议2. 环境与数据先盘点唯一键再盘点UPSERT2.1 MySQL的真实语义比“有就更新”复杂2.2 先建立 UPSERT 清单3. 复现过程最容易迁错的四种语义3.1 多唯一键冲突3.2 affected rows语义被应用依赖3.3 AUTO_INCREMENT / LAST_INSERT_ID依赖3.4 “后到的数据覆盖前到数据”不一定正确4. 方案实施三条迁移路线4.1 路线AMySQL兼容模式原样保留4.2 路线B改成显式 ON CONFLICT4.3 用 ON CONSTRAINT 表达稳定约束4.4 只去重不更新DO NOTHING4.5 增加版本保护4.6 RETURNING取代隐式主键推断4.7 多唯一键必须重建业务规则4.8 批量 UPSERT必须单独测试5. 结果对比并发验证比单线程结果更重要5.1 同一个request_id并发100次5.2 热点key与随机key要分开测5.3 版本乱序5.4 多唯一键冲突5.5 返回值验证5.6 数据校验5.7 示例性能结果6. 风险与复盘数据库幂等不等于业务幂等6.1 风险一唯一键选错6.2 风险二UPSERT覆盖了不该覆盖的字段6.3 风险三多唯一键导致冲突语义模糊6.4 风险四依赖affected rows判断业务动作6.5 风险五错误重试放大热点6.6 风险六UPSERT不等于乐观锁6.7 风险七分区表冲突更新限制回退方案按API级别回退UPSERT模板回退触发条件回退步骤最终复盘附录 AMySQL原写法附录 BKingbaseES ON CONFLICT附录 C只做幂等去重附录 D最低验收清单每日一句正能量“只有坚持长期主义将目光放长远才能逐渐达到理想的目标。”成功不是靠一时的爆发而是靠持续的积累不是计较一城一地的得失而是放眼整场人生战役。愿你在每一个当下都能这样温柔而坚定地与自己同行。慢慢来一步一步走时间会为长期主义者奉上最丰盛的礼物。 这份礼物就是你在过程中已然活出的、饱满而无悔的每一刻。主题幂等写入 / MySQL → KingbaseES / 接口入库服务重点ON DUPLICATE KEY UPDATE、ON CONFLICT、唯一键冲突、并发验证、返回值兼容、数据校验与回退适用场景订单回调、支付通知、事件入库、会员同步、第三方接口接收、CDC 落库、重复消息去重。1. 背景与问题ON DUPLICATE KEY迁移的不是SQL语法而是“重复请求怎么处理”的协议互联网接口入库服务里经常有这样的 MySQL SQLINSERTINTOapi_order_snapshot(request_id,order_no,order_status,amount,version_no,updated_at)VALUES(?,?,?,?,?,?)ONDUPLICATEKEYUPDATEorder_statusVALUES(order_status),amountVALUES(amount),version_noVALUES(version_no),updated_atVALUES(updated_at);开发人员通常把它理解成有就更新 没有就插入从业务上看它承担的往往是幂等写入第三方接口可能重试三次req-001 req-001 req-001系统希望最终只有一条业务记录。所以迁移到 KingbaseES 时第一个问题不应该是“KingbaseES 语法怎么写”而应该是到底是哪一个唯一键定义了“这是同一条业务请求”KingbaseES MySQL 兼容模式官方已经明确支持INSERT ... ON DUPLICATE KEY UPDATE因此如果目标是短周期兼容迁移源 SQL 可以优先原样验证。KingbaseES 同时还提供INSERT...ONCONFLICT(...)DOUPDATE并且ON CONFLICT DO UPDATE可以显式指定冲突列、唯一索引推断或唯一约束。这让我们可以把原来 MySQL 中一些隐式的冲突行为改造成显式业务协议。2. 环境与数据先盘点唯一键再盘点UPSERT示例环境源库MySQL 8.0/8.4 InnoDB 目标KingbaseES V9 MySQL兼容模式 系统订单接口入库服务 API QPS500020000 重复请求率0.5%5% 接口重试最多35次 迁移方式全量 CDC 应用灰度表CREATETABLEapi_order_snapshot(idBIGINTAUTO_INCREMENTPRIMARYKEY,request_idVARCHAR(64)NOTNULL,order_noVARCHAR(64)NOTNULL,order_statusVARCHAR(20)NOTNULL,amountDECIMAL(18,2)NOTNULL,version_noBIGINTNOTNULL,updated_atDATETIME(6)NOTNULL,UNIQUEKEYuk_request_id(request_id),UNIQUEKEYuk_order_no(order_no));这里已经出现第一个关键问题这张表有两个 UNIQUE KEY那么ONDUPLICATEKEYUPDATE到底哪个冲突算业务幂等2.1 MySQL的真实语义比“有就更新”复杂MySQL 8.4 官方文档明确说明如果 INSERT 导致PRIMARY KEY 或 UNIQUE INDEX重复则执行 UPDATE。但是如果表上有多个 UNIQUE 索引MySQL 文档给了一个非常值得注意的等价解释。例如a UNIQUE b UNIQUE插入a1,b2可能等价于UPDATEtSET...WHEREa1ORb2LIMIT1;如果不同唯一键匹配不同的旧行只更新其中一行MySQL 官方明确建议应尽量避免在拥有多个唯一索引的表上依赖ON DUPLICATE KEY UPDATE。这对迁移设计非常重要。因为 KingbaseESON CONFLICT DO UPDATE要求明确冲突目标时我们反而有机会把业务规则说清楚。2.2 先建立 UPSERT 清单建议扫描ON DUPLICATE KEY UPDATE REPLACE INTO INSERT IGNORE LAST_INSERT_ID ROW_COUNT affectedRows useGeneratedKeys记录api_id table primary_key unique_keys 真正幂等键 更新字段 是否覆盖NULL 是否使用版本号 是否读取affected rows 是否读取generated key 重试次数 并发QPS不要只搜索 SQL 字符串。很多 ORM 在 Mapper XML、动态 SQL、框架插件中生成 UPSERT。3. 复现过程最容易迁错的四种语义3.1 多唯一键冲突假设已有row A: request_idreq-001 order_noO001 row B: request_idreq-002 order_noO002新请求request_idreq-001 order_noO002这条数据同时request_id冲突A order_no冲突B这不是普通幂等重试。这意味着业务身份发生矛盾正确处理通常应该是拒绝 告警 人工/业务规则处理而不是数据库随便更新其中一行所以迁移时应把request_id或order_no中的一个明确为真正冲突键。3.2 affected rows语义被应用依赖MySQL 官方文档明确说明单行ON DUPLICATE KEY UPDATE新插入 → affected rows 1 更新已有行 → affected rows 2 更新成相同值 → affected rows 0如果客户端启用了CLIENT_FOUND_ROWS最后一种又可能返回 1。很多老 Java 代码写intnmapper.upsert(x);if(n1){// newly inserted}elseif(n2){// duplicate updated}这种逻辑迁移后非常危险。因为affected rows已经被当成了业务接口。迁移到 KingbaseES 时必须实际验证驱动返回值或者更稳妥地让 SQL 明确RETURNING所需字段而不是继续猜 rowcount。3.3 AUTO_INCREMENT / LAST_INSERT_ID依赖MySQL 官方说明如果表包含 AUTO_INCREMENTON DUPLICATE KEY UPDATE无论最终是插入还是更新都涉及 AUTO_INCREMENT /LAST_INSERT_ID()的特定连接级语义。因此需要扫描SELECTLAST_INSERT_ID();以及getGeneratedKeys()调用。如果应用在 UPDATE 路径仍希望拿到已有记录IDKingbaseES 目标 SQL 可以更明确RETURNINGid;不要假设 MySQL 连接态行为自然复制过来。3.4 “后到的数据覆盖前到数据”不一定正确典型接口12:00 收到 version12 12:01 收到重试/乱序 version11普通 UPSERTDOUPDATESETstatusEXCLUDED.status,version_noEXCLUDED.version_no;会把12降回11数据库语法没错但业务出现旧事件覆盖新事件。因此迁移是一个很好的机会加入WHEREtarget.version_noEXCLUDED.version_no让幂等写入具备版本保护。4. 方案实施三条迁移路线4.1 路线AMySQL兼容模式原样保留KingbaseES 官方 MySQL 兼容性总览明确说明支持 INSERT ON DUPLICATE KEY 子句所以最低风险做法INSERT...ONDUPLICATEKEYUPDATE...先保持。适合上线窗口紧 SQL简单 唯一键明确 没有特殊rowcount/LAST_INSERT_ID依赖但即使原样兼容也要跑完整并发回归。4.2 路线B改成显式 ON CONFLICT这是长期更推荐的路线。假设request_id才是接口幂等键。目标INSERTINTOapi_order_snapshot(request_id,order_no,order_status,amount,version_no,updated_at)VALUES(:request_id,:order_no,:order_status,:amount,:version_no,:updated_at)ONCONFLICT(request_id)DOUPDATESETorder_statusEXCLUDED.order_status,amountEXCLUDED.amount,version_noEXCLUDED.version_no,updated_atEXCLUDED.updated_at;这里最大的改进是冲突目标明确写出来了order_no如果冲突不会被当作幂等request_id默默处理而应该进入唯一约束异常。4.3 用 ON CONSTRAINT 表达稳定约束KingbaseESON CONFLICT还支持ONCONFLICTONCONSTRAINTuk_request_idDOUPDATE...这对约束命名规范稳定的系统很清晰。不过官方文档也建议很多情况下使用唯一索引推断更灵活因为底层索引替换后仍能继续推断。选择列推断还是命名约束属于治理风格问题。4.4 只去重不更新DO NOTHING事件入库event_id第一次插入重复消息什么也不做适合INSERTINTOinbound_event(event_id,payload)VALUES(...)ONCONFLICT(event_id)DONOTHING;这比重复时把相同payload更新一遍更明确也减少不必要 UPDATE。4.5 增加版本保护目标ONCONFLICT(request_id)DOUPDATESETorder_statusEXCLUDED.order_status,amountEXCLUDED.amount,version_noEXCLUDED.version_no,updated_atEXCLUDED.updated_atWHEREapi_order_snapshot.version_noEXCLUDED.version_no;现在version 12不会被version 11覆盖。这让幂等升级成幂等 防乱序4.6 RETURNING取代隐式主键推断KingbaseESINSERT ... ON CONFLICT支持RETURNING可以RETURNINGid,request_id,version_no;应用拿到真实数据库行而不是根据affected rows猜INSERT还是UPDATE或者依赖连接态LAST_INSERT_ID()。4.7 多唯一键必须重建业务规则如果request_id UNIQUE order_no UNIQUE third_party_no UNIQUE不能简单写任意unique冲突都算幂等应该建立request_id → 请求级幂等 order_no → 业务订单唯一性 third_party_no → 外部业务唯一性其中只有request_id作为 UPSERT 冲突键。其他唯一键冲突业务错误直接失败。这是从 MySQL 隐式规则迁成显式规则最有价值的一步。4.8 批量 UPSERT必须单独测试KingbaseES 官方ON CONFLICT DO UPDATE文档明确说明这是“确定性”语句不允许一次命令影响同一已有行超过一次如果待插入批次内部request_id重复可能产生基数违背错误。所以批量接口1000 rows要在应用侧先按冲突键去重或者分批处理。不要拿单行 UPSERT 测试结果直接推断批量语义。5. 结果对比并发验证比单线程结果更重要5.1 同一个request_id并发100次100线程request_idreq-001同时写。最终必须COUNT(*) WHERE request_idreq-001 1允许多次更新但不能出现重复插入5.2 热点key与随机key要分开测随机10000个request_id和热点同一个request_id更新10000次完全不是同一种负载。热点场景要观察P95/P99 行锁等待 死锁 CPU TPS因为 UPSERT 本身可能成为单行串行化热点。5.3 版本乱序先version12再version11断言最终version12再反过来11 →12最终125.4 多唯一键冲突构造request_id匹配row A order_no匹配row B目标设计如果已经明确ON CONFLICT(request_id)那么order_no冲突应该明确失败而不是更新未知目标行。这正是规范化后的正确行为。5.5 返回值验证对以下三种情况INSERT UPDATE DO NOTHING真实 JDBC/MyBatis 驱动测试affected rows RETURNING generated key 异常类型然后调整应用逻辑。不能凭 MySQL 旧经验猜。5.6 数据校验至少检查SELECTrequest_id,COUNT(*)FROMapi_order_snapshotGROUPBYrequest_idHAVINGCOUNT(*)1;应该0 rows同样检查order_no以及SUM(amount) MAX(version_no) status分布 更新时间5.7 示例性能结果场景MySQLKingbase兼容写法ON CONFLICT方案随机key 50并发 P958ms9ms8ms同key热点 P9545ms48ms46ms重复数据000旧版本覆盖新版本会发生会发生通过WHERE阻止多唯一键歧义存在兼容存在显式消除这些是评估模板示例不是本文声称的真实生产成绩。6. 风险与复盘数据库幂等不等于业务幂等6.1 风险一唯一键选错如果真正业务幂等键是request_id却使用order_no那么一次订单更新的不同接口请求可能被错误合并。反过来如果使用request_id但每次重试都生成新 request_id数据库唯一约束也救不了业务。所以幂等key必须在接口协议层先定义。6.2 风险二UPSERT覆盖了不该覆盖的字段例如重复请求第一次已有risk_flag1第二次 payload 没有这个字段却写risk_flagEXCLUDED.risk_flag可能把它改成默认0/NULL因此更新列应该按业务含义选择不要所有列全部覆盖6.3 风险三多唯一键导致冲突语义模糊这是从 MySQL 迁移时最值得治理的问题之一。MySQL 官方本身就建议尽量避免在多 UNIQUE 索引表上依赖ON DUPLICATE KEY UPDATE。KingbaseESON CONFLICT显式指定 arbiter 的能力正好用于消除这种歧义。6.4 风险四依赖affected rows判断业务动作这是典型历史代码债。建议迁移后不要通过1/2/0猜业务结果而使用RETURNING 显式状态 业务日志如果短期无法改则必须在兼容模式下实测返回值。6.5 风险五错误重试放大热点同一个热点订单超时后客户端5次重试 网关3次重试 MQ再次投递最终同 key 可能瞬间几十次 UPSERT。数据库唯一键保证不重复插入但锁争用仍然存在。因此需要有限重试 指数退避 请求幂等key 应用侧去重共同治理。6.6 风险六UPSERT不等于乐观锁如果两个线程A读取version10 B读取version10都修改不同业务字段。普通 UPSERT 最后写入者可能覆盖前者。真正需要并发控制时要增加version条件或单独的乐观锁规则。6.7 风险七分区表冲突更新限制KingbaseES 官方 INSERT 文档指出ON CONFLICT DO UPDATE当前不支持通过更新冲突行的分区键把行移动到新的分区。所以如果 UPSERT 会修改partition_key必须单独设计DELETE INSERT或业务禁止这种更新。回退方案按API级别回退UPSERT模板推荐api_id mysql_sql kingbase_compat_sql kingbase_on_conflict_sqlfeature flagorder.callback → on_conflict发现问题order.callback → mysql/compat不要把所有接口一次切换。回退触发条件重复业务key 0 旧版本覆盖新版本 0 关键状态分布差异 多唯一键冲突异常 P95 源系统150% generated key/返回值不兼容 死锁明显增加回退步骤1. 停止扩大Kingbase灰度 2. api_id切回旧SQL/旧库 3. 固化最后成功CDC水位 4. 比对Kingbase窗口期写入 5. 反向同步必要增量 6. 校验MySQL唯一键和AUTO_INCREMENT 7. 恢复MySQL主写 8. 保存失败参数、锁等待和执行日志复盘如果只是ON CONFLICT改写问题而 KingbaseES 数据库本身稳定也可以从ON CONFLICT版本 → 切回KingbaseES兼容ON DUPLICATE KEY版本不一定需要整库回源。最终复盘MySQL 到 KingbaseES 的ON DUPLICATE KEY UPDATE迁移可以分三层第一层兼容 KingbaseES MySQL模式原生支持 ON DUPLICATE KEY UPDATE 第二层规范化 显式改为 ON CONFLICT(conflict_key) DO UPDATE 第三层业务强化 版本保护 RETURNING 显式多唯一键规则 并发与热点治理如果只记住一句话UPSERT 的核心不是“INSERT失败就UPDATE”而是明确哪一个业务唯一键拥有把两次请求判定为“同一次写入”的权力。这个规则一旦明确数据库语法只是实现手段。附录 AMySQL原写法INSERTINTOapi_order_snapshot(request_id,order_no,order_status,amount,version_no)VALUES(?,?,?,?,?)ONDUPLICATEKEYUPDATEorder_statusVALUES(order_status),amountVALUES(amount),version_noVALUES(version_no);附录 BKingbaseES ON CONFLICTINSERTINTOapi_order_snapshot(request_id,order_no,order_status,amount,version_no)VALUES(:request_id,:order_no,:order_status,:amount,:version_no)ONCONFLICT(request_id)DOUPDATESETorder_statusEXCLUDED.order_status,amountEXCLUDED.amount,version_noEXCLUDED.version_noWHEREapi_order_snapshot.version_noEXCLUDED.version_noRETURNINGid,request_id,version_no;附录 C只做幂等去重INSERTINTOinbound_event(event_id,payload)VALUES(:event_id,:payload)ONCONFLICT(event_id)DONOTHING;附录 D最低验收清单[ ] ON DUPLICATE KEY SQL已全部扫描 [ ] REPLACE/INSERT IGNORE已扫描 [ ] 每张表PK/UK已盘点 [ ] 每条UPSERT的真正业务冲突键已确认 [ ] 多唯一键冲突已构造测试 [ ] affected rows依赖已扫描 [ ] LAST_INSERT_ID/generated key依赖已扫描 [ ] 版本乱序保护已验证 [ ] 单key 100并发通过 [ ] 随机key 100并发通过 [ ] 热点key压测完成 [ ] 批量UPSERT重复key已验证 [ ] 唯一键重复数0 [ ] 最终业务状态一致 [ ] api_id级回退开关已验证转载自https://blog.csdn.net/u014727709/article/details/163729080欢迎 点赞✍评论⭐收藏欢迎指正