MySQL中ON DUPLICATE KEY UPDATE的使用

需求背景：

现在有Test1表，Test2表，Test3表三张表
现在Test3表的数据都来源于Test1表和Test2表join后的结果
现在要求从Test1表和Test2表每天都要定时将数据导入到C表

需要用到判断主键或唯一索引在现有表中是否存在，存在就Update行数据，不存在就执行Insert

准备工作

drop table Test1;
CREATE TABLE `Test1` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into Test1 (id,name,sex) values(1,'小明','男');
insert into Test1 (id,name,sex) values(2,'小王','男');
insert into Test1 (id,name,sex) values(3,'小洁','女');


drop table Test2;
CREATE TABLE `Test2` (
  `id` int(11) NOT NULL,
  `subject` varchar(50) NOT NULL,
  `grade` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into Test2 (id,subject,grade) values(1,'数学',100);
insert into Test2 (id,subject,grade) values(1,'语文',90);
insert into Test2 (id,subject,grade) values(1,'英语',99);
insert into Test2 (id,subject,grade) values(2,'数学',66);
insert into Test2 (id,subject,grade) values(2,'语文',77);
insert into Test2 (id,subject,grade) values(2,'英语',88);
insert into Test2 (id,subject,grade) values(3,'数学',55);
insert into Test2 (id,subject,grade) values(3,'语文',44);
insert into Test2 (id,subject,grade) values(3,'英语',33);


drop table Test3;
CREATE TABLE `Test3` (
  `update_times` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(50) NOT NULL,
  `subject` varchar(50) NOT NULL,
  `grade` int(11) NOT NULL,
   UNIQUE KEY `idx_tb_basesemdt_offset_margin_stock_1` (`id`,`name`,`sex`,`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
insert into Test3 (update_times,id,name,sex,subject,grade) values(1,1,'小明','男','数学',100);
insert into Test3 (update_times,id,name,sex,subject,grade) values(1,2,'小王','男','数学',66);
insert into Test3 (update_times,id,name,sex,subject,grade) values(1,3,'小洁','女','数学',100);

实现思路

insert into Test3 (update_times,id,name,sex,subject,grade)
select 
	1 as update_times,
	a.id,
	a.name,
	a.sex,
	b.subject,
	b.grade
from Test1 a
left join Test2 b
on a.id = b.id
ON DUPLICATE KEY UPDATE                                   
update_times = update_times +1,id = a.id,name = a.name,sex = a.sex,subject = b.subject,grade = b.grade;

注：
这里update_times为更新次数，若每次更新某个唯一索引，该字段对应修改 update_times +1
这里我设置的唯一索引是 `id`,`name`,`sex`,`subject`
当这四个字段完全重复时就执行update语句，否则就是insert