在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语句而不是更新语句。
insert into kid_score(id, birth_day, score) values (1,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;
结果:
insert into kid_score(id, birth_day, score) values (2,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;
结果:
insert into kid_score(id, birth_day, score) values (2,'2019-01-16',20) ON DUPLICATE KEY UPDATE score = 20;
结果:
需要注意的是:如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2,如果更新的数据和已有的数据一模一样,则受影响的行数是0。
mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day | score |
+----+------------+-------+
| 1 | 2019-01-15 | 10 |
| 2 | 2019-01-16 | 20 |
+----+------------+-------+
2 rows in set
-- 唯一索引重复,执行更新
mysql> insert into kid_score(id, birth_day, score) values (1,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;
Query OK, 2 rows affected
mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day | score |
+----+------------+-------+
| 1 | 2019-01-15 | 60 |
| 2 | 2019-01-16 | 20 |
+----+------------+-------+
2 rows in set
-- 唯一索引不重复,执行插入
mysql> insert into kid_score(id, birth_day, score) values (2,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;
Query OK, 1 row affected
mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day | score |
+----+------------+-------+
| 1 | 2019-01-15 | 60 |
| 2 | 2019-01-15 | 30 |
| 2 | 2019-01-16 | 20 |
+----+------------+-------+
3 rows in set
-- 唯一索引重复,应该执行更新,但更新值与原值相同
mysql> insert into kid_score(id, birth_day, score) values (2,'2019-01-16',20) ON DUPLICATE KEY UPDATE score = 20;
Query OK, 0 rows affected
mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day | score |
+----+------------+-------+
| 1 | 2019-01-15 | 60 |
| 2 | 2019-01-15 | 30 |
| 2 | 2019-01-16 | 20 |
+----+------------+-------+
3 rows in set
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/qq_43842093/article/details/121437806
内容来源于网络,如有侵权,请联系作者删除!