MySQL on duplicate key update

最新推荐文章于 2023-11-11 13:07:51 发布

hello fa

最新推荐文章于 2023-11-11 13:07:51 发布

阅读量455

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： MySQL on duplicate key update

本文链接：https://blog.youkuaiyun.com/SILK_/article/details/103330913

数据库专栏收录该内容

1 篇文章

订阅专栏

本文深入探讨MySQL中onDuplicateKeyUpdate的功能与实践，介绍如何在数据同步时智能处理insert与update操作，避免重复数据冲突，同时提供具体示例和测试结果，帮助读者掌握这一高效的数据管理技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

项目中需要临时做一些数据抽取转换同步，例如将A、B、C表的数据进行筛选关联，再加入表D中，那么一条数据初次同步时，可以直接insert，但是第二次同步时就应当选择update。我最开始很蠢的通过查询判断是否已存在再选择insert或者update的方式实现，在做完了之后才想起来mysql自带了类似功能的实现，就是on duplicate key update。

为加深映像，写了这篇文章记录下使用心得吧。

语法：

insert into

table_name( field1, field2,...fieldN )

values( value1, value2,...valueN )

on duplicate key update

field2=values(field2), ...,valueN=values(valueN );

其中on duplicate key update后面接的就是主键或者唯一索引发生冲突时要更新的字段，但是如果这些字段本身又触发了唯一索引重复，那么还是会报错。

下面是一些具体的使用情况：

创建测试表demo，语句如下：

CREATE TABLE "demo" (
  "id" bigint(20) NOT NULL,
  "a" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "b" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "c" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "d" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "e" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY ("id"),
  UNIQUE KEY "unique_a" ("a") USING BTREE,
  UNIQUE KEY "unique_b" ("b") USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

现有数据内容如下，后面每一步操作之前都会将数据重置为如下内容，其中字段id、a和b都要求唯一：

MariaDB [demo]> select * from demo;
+----+------+------+------+------+------+
| id | a    | b    | c    | d    | e    |
+----+------+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    | 1    |
|  2 | 2    | 2    | 2    | 2    | 2    |
|  3 | 3    | 3    | 3    | 3    | 3    |
|  4 | 4    | 4    | 4    | 4    | 4    |
|  5 | 5    | 5    | 5    | 5    | 5    |
|  6 | 6    | 6    | 6    | 6    | 6    |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)

示例1：

insert into demo(id,a,b,c,d,e) 
values('1','7','7','7','7','7')
on duplicate key update
a=values(a), b=values(b), c=values(c), d=values(d), e=values(e);

返回结果如下：

Query OK, 2 rows affected (0.00 sec)
更新成功，注意受影响的行数是2。

示例2：

insert into demo(id,a,b,c,d,e) 
values('1','7','7','7','7','7'),('2','3','8','8','8','8'),('3','9','9','9','9','9')
on duplicate key update
a=values(a), b=values(b), c=values(c), d=values(d), e=values(e);

其中第二行的字段a会和第三行更新前的字段a重复，返回结果如下：

Duplicate entry '3' for key 'unique_a'

但是如果将插入顺序修改为如下形式，是可以更新成功的：

insert into demo(id,a,b,c,d,e) 
values('1','7','7','7','7','7'),('3','9','9','9','9','9'),('2','3','8','8','8','8')
on duplicate key update
a=values(a), b=values(b), c=values(c), d=values(d), e=values(e);

如果不想调整顺序，但希望MySQL能更新正确的部分内容，可添加ignore实现：

insert ignore into demo(id,a,b,c,d,e) 
values('1','7','7','7','7','7'),('3','9','9','9','9','9'),('2','3','8','8','8','8')
on duplicate key update
a=values(a), b=values(b), c=values(c), d=values(d), e=values(e);

示例3：

如果，存在多个字段对应不同的行都有冲突，那么更新哪一行呢？

insert into demo(id,a,b,c,d,e)
values('1','2','3','7','7','7') 
on duplicate key update
c=values(c),d=values(d),e=values(e);

其中id(主键)、a、b都有冲突，更新结果如下，只更新了第一行数据，通过主键id确定：

MariaDB [demo]> select * from demo;
+----+------+------+------+------+------+
| id | a    | b    | c    | d    | e    |
+----+------+------+------+------+------+
|  1 | 1    | 1    | 7    | 7    | 7    |
|  2 | 2    | 2    | 2    | 2    | 2    |
|  3 | 3    | 3    | 3    | 3    | 3    |
|  4 | 4    | 4    | 4    | 4    | 4    |
|  5 | 5    | 5    | 5    | 5    | 5    |
|  6 | 6    | 6    | 6    | 6    | 6    |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)

如果只有a、b字段冲突呢？

insert into demo(id,a,b,c,d,e)
values('7','2','3','7','7','7') 
on duplicate key update
id=values(id), c=values(c), d=values(d), e=values(e);

更新结果如下，只更新了第二行数据，通过字段a确定：

MariaDB [demo]> select * from demo;
+----+------+------+------+------+------+
| id | a    | b    | c    | d    | e    |
+----+------+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    | 1    |
|  3 | 3    | 3    | 3    | 3    | 3    |
|  4 | 4    | 4    | 4    | 4    | 4    |
|  5 | 5    | 5    | 5    | 5    | 5    |
|  6 | 6    | 6    | 6    | 6    | 6    |
|  7 | 2    | 2    | 7    | 7    | 7    |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)

以上测试可以发现，当前id、a、b字段都冲突时，更新行以id为准，当a、b字段冲突时，更新行以a字段为准。

我猜测这个优先级是在创建表结构时，由唯一索引的先后顺序决定的，但需要额外注意的是，主键是否永远是最高优先级？

修改建表语句如下，颠倒索引顺序，数据不变内容：

CREATE TABLE "demo" (
  "id" bigint(20) NOT NULL,
  "a" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "b" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "c" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "d" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "e" varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  UNIQUE KEY "unique_b" ("b") USING BTREE，
  UNIQUE KEY "unique_a" ("a") USING BTREE,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

执行如下语句：

insert into demo(id,a,b,c,d,e)
values('1','2','3','7','7','7') 
on duplicate key update
c=values(c),d=values(d),e=values(e);

更新结果如下，依然是更新第一行，通过主键id确定：

+----+------+------+------+------+------+
| id | a    | b    | c    | d    | e    |
+----+------+------+------+------+------+
|  1 | 1    | 1    | 7    | 7    | 7    |
|  2 | 2    | 2    | 2    | 2    | 2    |
|  3 | 3    | 3    | 3    | 3    | 3    |
|  4 | 4    | 4    | 4    | 4    | 4    |
|  5 | 5    | 5    | 5    | 5    | 5    |
|  6 | 6    | 6    | 6    | 6    | 6    |
+----+------+------+------+------+------+

仅有a、b冲突时：

insert into demo(id,a,b,c,d,e)
values('7','2','3','7','7','7') 
on duplicate key update
id=values(id), c=values(c), d=values(d), e=values(e);

结果如下，更新行变成了第三行，由字段b冲突导致：

+----+------+------+------+------+------+
| id | a    | b    | c    | d    | e    |
+----+------+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    | 1    |
|  2 | 2    | 2    | 2    | 2    | 2    |
|  4 | 4    | 4    | 4    | 4    | 4    |
|  5 | 5    | 5    | 5    | 5    | 5    |
|  6 | 6    | 6    | 6    | 6    | 6    |
|  7 | 3    | 3    | 7    | 7    | 7    |
+----+------+------+------+------+------+

结论：

如果插入语句中存在多个唯一性冲突，那么更新目标行优先通过主键指定，如果主键并未冲突，那么更新目标行通过唯一索引的先后顺序进行指定。