MySQL on duplicate key update

本文深入探讨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    |
+----+------+------+------+------+------+

结论:

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值