SQL编程：REPLACE INTO

REPLACE INTO 语句是 SQL 标准的 MySQL 扩展，它的功能就是要么直接插入一条新记录，要么删除旧的记录后再插入一条新的记录。

如果归纳一下 REPLACE INTO 语句的执行规则，基本会有以下几种情况：

1. 当表没有主键和唯一键时，REPLACE INTO 相当于普通的 INSERT 操作；binlog 记录事件为 INSERT；返回影响行数为 INSERT 的数量。
2. 当表有主键没有唯一键时，REPLACE INTO 插入记录与主键冲突时会进行 DELETE+INSERT 操作；binlog 记录事件为 UPDATE；返回影响行数为 DELETE+INSERT 的数量；如果主键自增，则 AUTO_INCREMENT 值不变。
3. 当表有唯一键没有主键时，REPLACE INTO 插入记录与唯一键冲突时会进行 DELETE+INSERT 操作；binlog 记录事件为 UPDATE；返回影响行数为 DELETE+INSERT 的数量。
4. 当表同时存在主键和唯一键时，REPLACE INTO 插入记录与主键冲突的时候进行 DELETE+INSERT 操作；binlog 记录事件为 DELETE+INSERT；返回影响行数为 DELETE+INSERT 的数量。如果主键自增，则 AUTO_INCREMENT 值不变。
5. 当表同时存在主键和唯一键时，REPLACE INTO 插入记录与唯一键冲突的时候进行 DELETE+INSERT 操作；binlog 记录事件为 UPDATE；返回影响行数为 DELETE+INSERT 的数量。如果主键自增，则新插入的这条记录的主键会变成最新 AUTO_INCREMENT 的值，而对应的 AUTO_INCREMENT 值会 +1。
6. 当表同时存在主键和唯一键时，REPLACE INTO 插入记录与一条记录主键和一条记录唯一键都冲突的时候进行 DELETE+INSERT 操作；binlog 记录事件为 DELETE+UPDATE；返回影响行数为 DELETE*2+INSERT 的数量。

第 5 种形式会带来副作用，主键被改变了，并且 binlog 事件记录为 UPDATE，会导致两种异常情况。

在多数业务场景关系模型中，子表会依赖主表的自增主键，此时如果主表主键改变会导致与子表的关系对应不上，数据错乱。

主键自增值增加，但由于 binlog 事件记录为 UPDATE 会导致主从环境中表的 AUTO-INCREMENT 值不同，从库执行 UPDATE 事件并不会导致 AUTO-INCREMENT 值增加，所以从库表的 AUTO-INCREMENT 值会小于等于当前表的最新记录主键，当发生主从切换时向新的主库插入记录就会报 duplicate key 错误。

鉴于此，很多使用 REPLACE INTO 的场景，实际上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE（虽然也会增加自增值，但是不会出现从库表的 AUTO-INCREMENT 值会比当前表的最新记录主键小的情况），与 ‘REPLACE INTO ..’ 不同，它只是更新重复行上的值，没有删除，也就不会导致原有主键值的变化。

第 6 种也会带来副作用，记录被合并。

还需要注意所有列的值均取自在 REPLACE INTO 语句中被指定的值，所有缺失的列被设置为各自类的默认值，这和 INSERT 一样。你不能从当前行中引用值，也不能在新行中使用值。

总之，在正确理解 REPLACE INTO 行为和副作用的前提下，谨慎使用 REPLACE INTO。同时，使用 REPLACE INTO 你必须同时拥有表的 INSERT 和 DELETE 权限。

<参考>

MySQL “replace into” 的坑

MySQL replace into 浅析之一

MySQL replace into 浅析之二

喜欢 (1)赏

[资助本站您就扫码 谢谢]

分享 (0)