• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

SQL编程:REPLACE INTO

MySQL SQL 彭东稳 6年前 (2018-01-09) 27221次浏览 已收录 0个评论

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)

您必须 登录 才能发表评论!