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

SQL编程:INSERT … ON DUPLICATE KEY UPDATE

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

向数据库插入记录时,有时会有这种需求,当符合某种条件的数据存在时,去修改它,不存在时,则新增,也就是 insert Or Update 操作。这种控制可以放在业务层,也可以放在数据库层,大多数数据库都支持这种需求,如本文所讲的 MySQL 中的INSERT ... ON DUPLICATE KEY UPDATE语句。

该语句是基于唯一索引或主键使用,比如一个字段 a 被加上了 unique index,并且表中已经存在了一条记录值为 1,下面两个语句会有相同的效果:

ON DUPLICATE KEY UPDATE后面可以放多个需要更新的字段,用英文逗号分割。

使用ON DUPLICATE KEY UPDATE,最终对于影响行数和自增值的规则如下:

  • 如果插入了一个新行,则受影响的行数是 1;存在自增主键时,auto_increment+1;同时在 binlog 中的事件为 insert 事件。
  • 如果修改了已存在的一行数据,则受影响的行数是 2;存在自增主键时,auto_increment+1;同时在 binlog 中的事件为 update 事件。
  • 如果值不变,则受影响行数是 0;存在自增主键时,auto_increment+1;在 binlog 中没有事件。

为什么修改一行数据时,返回的受影响的行数是 2 呢?很容易造成误解。实际上,只是在代码实现上,insert 和 update 都认为自己成功了,update 计数加了 1, insert 计数也加了 1。

Warning

从上面的结果可以看出,只要使用了 ON DUPLICATE KEY UPDATE,auto_increment 都会 +1。

如果字段 b 也被加上了 unique index,则该语句和下面的 update 语句是等效的:

如果a=1 OR b=2匹配了多行,则只有一行会被修改。通常的,在ON DUPLICATE KEY UPDATE语句中,我们应该避免多个唯一索引的情况,这种用法被标记为不安全的(Bug#58637)。

可以在ON DUPLICATE KEY UPDATE后面使用 VALUES(field) 函数来表示即将插入的值,如果需要插入或更新多条数据,并且更新的字段需要根据其它字段来运算时,可以使用如下语句:

这个语句等同于下面的两个语句:

需要注意的下面的使用方式,特别是一些解析 binlog 的中间件可能会使用,在生产环境中也确实遇到过。

这里表中包含了一个 primary 自增主键,假设 a 字段是唯一索引,上述语句第一次执行时会正常插入一条语句,第二次执行时由于 a=1 已经存在就会进行更新操作,并且由于 primary=values(null) 的设置,会更新 primary=auto_increment 的值,也就是主键被更新了,二进制日志中同时会记录 update 事件。这是一个非常需要小心的操作,有可能你其他地方依赖你原来的主键。

当主键被更新后,可以通过last_insert_id()方法返回最后自动生成的值,如果是多条,实际测试是返回第一条数据自增的值。


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

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