Mysql INSERT ON DUPLICATE KEY UPDATE

导语

牛角里也有大天地

语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
  {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
  [ ON DUPLICATE KEY UPDATE
    col=expr
      [, col=expr] ... ]

简介

INSERT ... ON DUPLICATE KEY UPDATE是MySQL insert的一种扩展。当发现有重复的唯一索引(unique key)或者主键(primary key)的时候，会进行更新操作；如果没有，那么执行插入操作。

这样使用的好处是能够节省一次查询判断。如果有个业务的场景是，有过有这条数据，那么进行更新，如果没有，那么进行新增插入操作。

如果不使用INSERT ... ON DUPLICATE KEY UPDATE, 那么一种比较常见的解决思路是，先按照unque key查询，是否存在这条数据，如果不存在，直接新增。如果存在这条数据，那么比对其余值是否一致，如果不一致，那么则进行更新操作，否则什么都不需要操作。

注意事项

1. 当操作执行成功之后，affected-rows的值可能为0,1,2 返回值为1时：表里没有重复的记录，进行的是新增插入操作 返回值为2时：表里有重复的记录，但是其余值不一致，进行了update操作 返回值为0时：表里有重复的记录，并且其余值完全一致，无需update操作 当然，以上成立的条件是CLIENT_FOUND_ROWS没有被设置过。
2. 如果执行操作的表设置了AUTO_INCREMENT的自增主键，那么当有记录被Update或者insert的时候，LAST_INSERT_ID()函数将会返回AUTO_INCREMENT的值，这一点切记。
3. INSERT ... ON DUPLICATE KEY UPDATE强烈不推荐对拥有多个唯一索引(unique key)的表使用，除非你非常清醒地知道你在干什么会有什么样的后果。 一起来体会一下社区人的温馨提示吧：If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index. An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)
4. 执行该语句会触发insert和update的触发器(triggers)

示例

话不多说，上梭子。新建一张表，主键ID自增，另外有一个唯一索引playerId(球员ID), 另外一个字段是球员名。

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playerId` int(11) NOT NULL DEFAULT 0,
  `name` varchar(10) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `playerId` (`playerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后往里面插入两条测试数据：

insert into test(playerId, name) values(101, 'Jordan');

insert into test(playerId, name) values(102, 'James');

初始数据

接下来尝试一下没有重复的

insert into test(playerId, name) values(103, 'Zac');

返回：

Query OK, 1 row affected (0.19 sec)

这符合我们的预期

接下来尝试重复唯一索引，但是需要更改其余值的，这里的其余值指的是球员名(name)。

insert into test(playerId, name) values(101, 'Bob') ON DUPLICATE KEY UPDATE playerId = 101, name = 'Bob';

返回：

Query OK, 2 rows affected (0.05 sec)

这也符合我们的预期，但是我们看下AUTO_INCREMENT现在是多少.

3条记录，但是自增ID为5

我们只有三条记录，但是AUTO_INCREMENT已经是5了，正常逻辑应该是4才对。原因就在于，每当INSERT ... ON DUPLICATE KEY UPDATE执行成功时，AUTO_INCREMENT都会自增加1.

不管是否有数据变更，AUTO_INCREMENT都会自增加1。

接下来我们试一下没有数据变更的： insert into test(playerId, name) values(102, 'James') ON DUPLICATE KEY UPDATE playerId = 102, name = 'James';

返回:

Query OK, 0 rows affected (0.04 sec)

那么这个时候的AUTO_INCREMENT就应该是6了，尽管没有数据变更。

ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

接下来试一下，更新情况下的AUTO_INCREMENT。

insert into test(playerId, name) values(101, 'Carl') ON DUPLICATE KEY UPDATE playerId = 101, name = 'Carl';

返回：

Query OK, 2 rows affected (0.04 sec) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

而此时数据库里仍然只有3条记录：

3条记录，但是自增ID为7

现在我们试一下使用两个唯一索引会发生什么情况：

insert into test(id, playerId, name) values(2, 101, 'Danny') ON DUPLICATE KEY UPDATE id =2, playerId = 101, name = 'Danny';

返回：

ERROR 1062 (23000): Duplicate entry '101' for key 'playerId'

这就是为什么不推荐使用两个唯一索引的原因。

总结

每当INSERT ... ON DUPLICATE KEY UPDATE执行成功时，AUTO_INCREMENT都会自增加1。

这个很关键，切记。

引用资料

1. https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/
2. https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html