MySQL implementation Upsert

• Upsert (update or insert), that is, updates or writes.
• MySQL implementation of Upsert operation mode:
  Idea: Decide whether to insert or update by judging if there is a primary key index or a unique index conflict in the inserted record. An update operation occurs when a primary key index or a unique index conflict is encountered, otherwise an insert operation is performed.
  Implementation: Using the on DUPLICATE KEY UPDATE

Take a look at the specific implementation process below.

First, prepare the data sheet

CREATE TABLE `demo` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `a` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,  `b` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,  `c` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,  `d` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,  `e` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,  `f` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,  PRIMARY KEY (`id`),  UNIQUE KEY `unq_a_b_c` (`a`,`b`,`c`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Note: There are two indexes in the table, the ID is the primary key index, and the A,B,C is the Federated unique index.

Second, write the initial data

insert into test.demo(a,b,c,d,e,f) values(1,1,1,1,1,1);

At this point there is a unique index data consisting of the ABC hash: 1,1,1.

Third, further realize

insert into demo(a,b,c,d,e,f) values(1,1,1,2,2,2) ON DUPLICATE KEY UPDATE a=2,b=2,c=3,d=4,e=5,f=6;

因为已经存在由abc三列组成唯一索引数据：1，1，1，本次又写入demo(a,b,c,d,e,f) values(1,1,1,2,2,2)，会造成唯一索引冲突。因此，会触发ON DUPLICATE KEY 后面的 UPDATE a=2,b=2,c=3,d=4,e=5,f=6操作。

At this point, the Upsert functionality has been implemented. Please remember the usage of the on DUPLICATE KEY update.

MySQL implementation Upsert