项目中需要临时做一些数据抽取转换同步,例如将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 |
+----+------+------+------+------+------+
结论:
如果插入语句中存在多个唯一性冲突,那么更新目标行优先通过主键指定,如果主键并未冲突,那么更新目标行通过唯一索引的先后顺序进行指定。