ingrun

Appearance

Return to top
本页目录

mysql插入转更新ON DUPLICATE KEY UPDATE

语法：

sql
insert into table(key...) values(val...)on duplicate key update key1=val1,key2=val2
1

创建表user，下面的例子都以该表为主

sql
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NULL,
  `user_name` VARCHAR(45) NULL,
  `score` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC));
INSERT INTO user (id, user_id, user_name, score) VALUES (66, 1, 'tom', 97);
INSERT INTO user (id, user_id, user_name, score) VALUES (67, 2, 'marry', 95);
1
2
3
4
5
6
7
8
9

单条记录插入：

  • user_id为唯一键
sql
insert into user(user_id,user_name,score) values(1,'tom',100)on duplicate key update score=100
1

前半段sql是正常的insert语句 后半段sql从on duplicate key开始，update将会在主键/唯一键冲突时执行。 若数据库中已经存在user_id=1的数据，将会执行update操作，将user_id=1数据中的score改为100

多条记录插入：

user_id为唯一键

sql
insert into user(user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score=values(score)

insert intou ser(user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score=values(user_id)

insert into user(user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score=values(user_id)+values(score)

insert into user(user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score=values(66)
1
2
3
4
5
6
7
8
9
10
11
  • 多条记录插入时，需要利用values函数处理赋值
  • values内可以直接使用字段名，可以是当前被赋值的字段(正常情况)，也可以是其他字段
  • values内还可以是具体的常量值
  • 可以使用多个数字类型的字段经过values处理后再进行运算

细节：

  • 在使用insert into ... on duplicate key update插入/更新数据时，affected-rows在不同情况下的值不同，下面以插入单条记录为例

    • 当数据成功插入时（没有冲突），affected-rows=1(仅有insert一个操作，插入n条记录就是n)
    • 当数据发生冲突并更新成功时，affected-rows=2(包含delete和update操作，插入n条记录就是2*n)
    • 当数据发生冲突，更新内容与原数据一致时，affected-rows=0。若开启了CLIENT_FOUND_ROWS，affected-rows=1(冲突记录数量，n条记录冲突就是n)

  • 在冲突update时，若表中存在自增字段，自增字段也会+1(不管affects-rows是否>0)

    • 若id为自增，当前id为67，先执行一条有冲突的语句，此时自增id已经到68(原数据的id不会自增，只是自增id的计数器会+1)
    • 再插入一条新的记录，此时新纪录的id为69
sql
// 插入冲突数据，执行update，当前最新id自增
insert into user(user_id,user_name,score) values(2,'marry',87) on duplicate key update score=87 
// 插入一条新数据 
insert into user(user_id,user_name,score) values(3,'kiti',99)
1
2
3
4