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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Linyb极客之路

深入理解MySQL索引原理和实现——为什么索引可以加速查询?

说到索引,很多人都知道“索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询的速度,这...

1313
来自专栏待你如初见

Day15

983
来自专栏互联网开发者交流社区

数据定义: CREATE、DROP、ALTER

1372
来自专栏数据和云

层层升入:SQL极限调优之一次更新操作的N种优化可能

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 最近进行了一次更新操作,整个处理和优化的过...

3028
来自专栏Java帮帮-微信公众号-技术文章全总结

MySQL全部知识点(2)

6 聚合函数 聚合函数是用来做纵向运算的函数: l COUNT():统计指定列不为NULL的记录行数; l MAX():计算指定列的最大值,如果指定列是字符串类...

3487
来自专栏Java后端生活

MySQL(十二)DML数据的增删改

1556
来自专栏极客慕白的成长之路

SQL Sever基本知识

create table tablename(col_name1 type,col_name2 type,...) 在数据库school下创建一个student...

492
来自专栏性能与架构

Mysql Query Cache的基本原理

Query Cache是根据SQL语句来cache的,一个SQL查询如果以select开头,那么MySQL将尝试对其进行缓存 每个Cache都是以完整的SQL...

3425
来自专栏zingpLiu

python【第十二篇】Mysql基础

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。我们也可...

962
来自专栏极客慕白的成长之路

MySQL从安装到使用

Columns 列;Indexes 索引;Views 视图;Events 事件;Fields 字段;

794

扫码关注云+社区