注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

SQL编程:REPLACE INTO

MySQL SQL 彭东稳 3960次浏览 已收录 2个评论

REPLACE是SQL标准的MySQL扩展,它要么插入,要么删除。REPLACE工作原理与INSERT类似,不同之处在于假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除。INSERT … ON DUPLICATE KEY UPDATE是直接更新旧行。要使用REPLACE,你必须同时拥有表格INSERT和DELETE权限。

另外REPLACE只有当一个表有一个PRIMARY KEY或UNIQUE索引时才有意义。否则,它就等同于INSERT语句,因为没有用于确定新行跟旧行是否重复的凭证。

所有列的值均取自在REPLACE语句中被指定的值,所有缺失的列被设置为各自的默认值,这和INSERT一样。你不能从当前行中引用值,也不能在新行中使用值。如果你使用一个例如“SET col_name = col_name + 1”的赋值,则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此,该赋值相当于SET col_name = DEFAULT(col_name) + 1。

REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1,则一行被插入,同时没有行被删除。如果该数大于1,则在新行被插入前,有一个或多个旧行被删除。如果表包含多个唯一索引,并且新行复制了在不同的唯一索引中的不同旧行的值,则有可能是一个单一行替换了多个旧行。

那么总结一下对表进行replace into操作的时候,会有以下几种情况:

  • 当不存在冲突时,replace into相当于insert普通的操作。
  • 当表有主键没有唯一键时,replace into会进行update操作。
  • 当表有唯一键没有主键时,replace into会进行update操作。
  • 当表同时存在主键和唯一键时,当pk冲突的时候是先delete再insert。如果主键自增,则AUTO_INCREMENT属性不变。
  • 当表同时存在主键和唯一键时,当uk冲突的时候是直接update。如果主键自增的,则AUTO_INCREMENT属性会做+1操作。

测试步骤具体参考下面几篇文章:

MySQL “replace into” 的坑

MySQL replace into 浅析之一

MySQL replace into 浅析之二


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (1)or分享 (0)
关于作者:

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

(2)个小伙伴在吐槽
  1. 当存在pk冲突的时候是先delete再insert。如果主键自增,则AUTO_INCREMENT属性不变。 当存在uk冲突的时候是直接update。如果主键自增的,则AUTO_INCREMENT属性会做+1操作。 ------我在5.7的版本上测试的结果都是直接update,切AUTO_INCREMENT属性不变,求指导?
    gaotuan2018-01-25 11:29 Windows 7 | Chrome 63.0.3239.132
    • 你要看了下面几篇文章就知道了。指的是一个表同时存在主键和唯一键时。
      彭东稳2018-01-25 12:19 未知操作系统 | Chrome 63.0.3239.132