浅记一笔：insert into ... on duplicate key update ...

一、简介

都知道insert into是新增数据，update 是更新数据。一般都是在业务中判断这条数据是否存在，如不过不存在就新增，存在就更新。如果不在业务中处理，交给数据库来做呢？

MySQL有个语法可以解决：insert into ... on duplicate key update ...

二、验证

2.1、初始化数据

新建一张测试表

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `grade` int DEFAULT NULL COMMENT '等级 索引1',
  `sort` int DEFAULT NULL COMMENT '排序 索引2',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_grade_name_uindex` (`grade`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试表';

# 初始化数据
insert into test (name, sex, grade, sort) 
values ('张三', 0, 0, 0), ('李四', 1, 1, 1), ('王五', 0, 2, 3);

瞅瞅初始化后的数据

# 查看数据
select * from test;

注：除主键外，还建了一个test_grade_name_uindex唯一索引

2.2 基于唯一索引执行

执行第一次 insert into ... on duplicate key update ...

# 插入数据1
insert into test (`name`, `sex`, `grade`, `sort`)
values ('赵六', 0, 1, 7),
       ('田七', 0, 2, 8)
on duplicate key update `sex`  = values(`sex`),
                        `sort` = values(`sort`);

唯一索引里面没有“赵六-1”和“田七-2”，所以新增了

执行第二次 insert into ... on duplicate key update ... 稍微改下数据

# 插入数据2
insert into test (`name`, `sex`, `grade`, `sort`)
values ('赵六', 1, 1, 9),
       ('田七', 1, 2, 10)
on duplicate key update `sex`  = values(`sex`),
                        `sort` = values(`sort`);

唯一索引里面已经有“赵六-1”和“田七-2”，所以进行了更新

2.3、基于主键索引执行

执行一次 insert into ... on duplicate key update ...

# 插入数据
insert into test (`id`, `sex`, `sort`)
values (4, 0, 11),
       (5, 0, 12)
on duplicate key update `sex`  = values(`sex`),
                        `sort` = values(`sort`);

三、基于主键索引和其他唯一索引执行时的区别

基于主键索引进行更新操作时，自增主键不会自增；而非主键索引进行更新时，仍会自增。

初始化数据完只有三条数据，此时自增值是4。
前面2.2执行了两次非主键索引插入更新，此时自增值增加是4+2+2=8。（2是因为更新了2条数据）
前面2.3了执行一次主键索引插入更新，此时自增值不变仍是8。

那么接下来新增一条数据，它的主键就是8。

# 插入一条数据
insert into test (name, sex, grade, sort) values ('王二麻子', 0, 0, 0);

至于为什么，我还不知道，可以去看看别人写的。
我就做个笔记，等我知道了再来更新。