mysql 插入或更新ON DUPLICATE KEY UPDATE

CREATE TABLE `zzz` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `names` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `kecheng` varchar(80) CHARACTER SET utf8mb4 NOT NULL COMMENT '课程',
  `score` int(11) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx` (`names`,`kecheng`)
) ENGINE=InnoDB;

insert into `zzz` (`id`, `names`, `kecheng`, `score`) values('1','a','语文','80');
insert into `zzz` (`id`, `names`, `kecheng`, `score`) values('2','b','语文','85');
insert into `zzz` (`id`, `names`, `kecheng`, `score`) values('3','a','数学','90');
insert into `zzz` (`id`, `names`, `kecheng`, `score`) values('4','b','数学','77');
insert into `zzz` (`id`, `names`, `kecheng`, `score`) values('5','a','英语','80');
insert into `zzz` (`id`, `names`, `kecheng`, `score`) values('6','b','英语','88');

names 和 kecheng 已经建立了unique索引, 所以当重复插入names和kecheng同时重复的记录时会报错, 这时希望更新score, 可以这样写:

insert into zzz(names,kecheng,score) values('a','语文',10) on duplicate key update score=90;

1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into zzz(names,kecheng,score) values('a','语文',10) on duplicate key update score=90

共 2 行受到影响

执行耗时   : 0.056 sec
传送时间   : 0 sec
总耗时      : 0.056 sec

2行受到影响, 先尝试插入,插入不成功然后删除掉不成功的数据, 再执行修改,相当于一条删除,一条更新, 因此2行受影响.
何以见得?
可以接着试验,之前主键id已经增长到6, 执行这条sql后, 再执行正常的插入(非重复数据), 发现id是8而不是7, 说明7已经被使用过了,只不过7那条数据插入不成功被删除掉了.


[b]如果有重复数据,但是不想更新任何字段呢?[/b]
比如重复数据直接忽略,不更新score字段,可以这样:

INSERT INTO zzz(NAMES,kecheng,score) VALUES('a','语文',10) ON DUPLICATE KEY UPDATE score=score;

1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into zzz(names,kecheng,score) values('a','语文',10) on duplicate key update score=score

共 0 行受到影响

执行耗时   : 0.049 sec
传送时间   : 0 sec
总耗时      : 0.049 sec

受影响0行, 说明没有任何改动


[b]如果有重复数据,想更新多个字段呢?[/b]

INSERT INTO zzz(NAMES,kecheng,score) VALUES('a','语文',10) ON DUPLICATE KEY UPDATE score=score,kecheng=kecheng;

1 queries executed, 1 success, 0 errors, 0 warnings

查询：insert into zzz(names,kecheng,score) values('a','语文',10) on duplicate key update score=score

共 0 行受到影响

执行耗时   : 0.049 sec
传送时间   : 0 sec
总耗时      : 0.049 sec