Usage, advantages and disadvantages of MySQL on duplicate key update

Hello everyone , I meet you again , I'm the king of the whole stack .

in application , We often encounter the function of importing data , Add when the imported data does not exist , If there is any modification, it will be updated ,

　　 When I first met , The general idea is to divide its implementation into two parts , Respectively, judgment increases , Judge update , Later it was found in mysql There is ON DUPLICATE KEY UPDATE One step can be done (Mysql Unique grammar ).

ON DUPLICATE KEY UPDATE Single update and batch update sql

stay MySQL In the database , If in insert The statement is followed by ON DUPLICATE KEY UPDATE Clause , The row to be inserted has duplicate values in the unique index or primary key of the existing record in the table , Then there will be an update of the old line ; If the inserted row data does not duplicate the unique index or primary key of the record in the existing table , Then execute the new record insertion operation . Generally speaking, when there is a record in the database , Executing this statement will update , When this record doesn't exist , It will insert .

Be careful ：

　　 Because this is an insert statement , So you can't add where Conditions .

　　 If it's an insert operation , The value of the affected row is 1; If the update operation , The value of the affected row is 2; If the updated data is the same as the existing data （ It's equivalent to not changing , All values remain unchanged ）, The value of the affected row is 0.

This statement is based on a unique index or primary key , For example, a field a Was added unique index, A record already exists in the table 1,

The following two statements will have the same effect ：

INSERT INTO table (a,b,c) VALUES (1,2,3)  
  ON DUPLICATE KEY UPDATE c=c+1;  
  
UPDATE table SET c=c+1 WHERE a=1;

ON DUPLICATE KEY UPDATE Multiple fields can be placed after , Use English commas to separate .

Reproduce an example ：

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
      ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); 

Change in table （ To add or modify ） Two records .

The operation of data in a project can sometimes be overwhelming , Meet a need ：

Need to transfer data from A Database a Data table synchronization to B Database b In the data table （ab The table structure is the same , But it's not a master-slave relationship ...just Synchronize the past ）

Sync the past for the first time ,b Table is empty , Synchronization is simple .

But when a surface Medium Some data is updated and new data is added after , It's a little troublesome to synchronize the two tables .（ If you put b The watch is empty , To resynchronize , Too much data takes too long , Not a good way ）

Think about whether you can update according to the time period , There are new data during this period , Just insert the data , Update the data when there is data update . Let me start with my thoughts ：

step ：

　　1. First of all, I start from a The table takes out the data of a certain period （ Segment update ）

　　2. Go to b Data in table , Judge according to the primary key b Whether the table has this record , If there is no such data, insert , If there is a record, whether the comparison data is the same , The same will not be changed , Update if it's different .

At this time, using this statement can meet the needs , But to Pay attention to a few questions ：

• In the updated content unique key perhaps primary key It's best to guarantee one , Otherwise, the correct execution of the statement cannot be guaranteed ( There is any one unique key Repeat and update , Of course, if there are double checked fields in the table in the updated statement , Then it will not be updated successfully and cause an error , Only if there is no one in this statement unique key New records will be inserted only after repetition ); Try not to have multiple unique keys table Use this statement , Avoid data confusion .
• Where there may be concurrent transactions insert Statement is not used , May cause death lock.
• If the data sheet id This statement is not recommended for automatic increment ;id Discontinuous , If the previous updates are more , The new next one will jump larger .
• The statement is mysql Unique grammar , If it is possible to design other database languages, cross database should be used with caution .

produce death lock principle

insert … on duplicate key When executed ,innodb The engine will first determine whether the inserted rows are duplicated key error , If there is , Add... To the existing row S（ Shared lock ） lock , If this row of data is returned to mysql, then mysql After execution duplicate After update operation , Then add... To the record X（ Exclusive lock ）, The last part update write in .

If two transactions execute the same statement concurrently , Then there will be death lock, Such as ：

Publisher ： Full stack programmer stack length , Reprint please indicate the source ：https://javaforall.cn/111490.html Link to the original text ：https://javaforall.cn