MySQL的INSERT ··· ON DUPLICATE KEY UPDATE使用的几种情况

MySQL INSERT ON DUPLICATE KEY UPDATE 操作详解

最新推荐文章于 2024-07-30 11:11:13 发布

原创最新推荐文章于 2024-07-30 11:11:13 发布 · 924 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库专栏收录该内容

7 篇文章

订阅专栏

MySQL的INSERT ··· ON DUPLICATE KEY UPDATE语句在遇到唯一索引重复时会更新已有记录，而当插入数据与现有记录不冲突时则执行插入操作。文章通过三个示例展示了不同情况下该语句的行为：1) 唯一索引重复时，执行更新；2) 唯一索引不重复时，执行插入；3) 唯一索引重复且数据相同，仍更新记录。

在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。另外，ON DUPLICATE KEY UPDATE不能写where条件。

示例：

create table kid_score(
id tinyint unsigned not null,
birth_day date not null,
score int unsigned not null,
primary key(id, birth_day)  --唯一索引是由 id + birth_day 两个字段组成
) engine = InnoDB;
--初始化数据
insert into kid_score(id, birth_day, score) values (1,'2019-01-15',10),(2,'2019-01-16',20);

下面开始验证执行INSERT ··· ON DUPLICATE KEY UPDATE语法的规则：如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。

1. 唯一索引重复

insert into kid_score(id, birth_day, score) values (1,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;

结果：

2. 唯一索引不重复

insert into kid_score(id, birth_day, score) values (2,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;

结果：

3. 唯一索引重复，插入完全相同数据

insert into kid_score(id, birth_day, score) values (2,'2019-01-16',20) ON DUPLICATE KEY UPDATE score = 20;

结果：