Insert into XXX on duplicate key update and replace into in MySQL

These two statements are for implementation: insert if there is no record, and update if there is a record. Note that these two statements are specific to MySQL and are not standard SQL statements. Its implementation is similar to the following:

 Select   1   From T Where ID =  1  ;ProgramDetermine whether a value exists  If  (Exists ){  Update T Set Update_time =   ' 2012  '   Where ID =   1  }  Else  {  Insert   Into T (ID, update_time) Values ( 1 , '  2012  '  )} 

1)ReplaceImplement the above logic:

 
Replace IntoT (ID, update_time)Values(1,'2012'); Or:Replace IntoT (ID, update_time)Select 1,'2012';
 

ReplaceExecution Process:

First, try to insert data to the table. 1. If the table already has this row of data (determined based on the primary key or unique index), delete this row of data and insert new data. 2. Otherwise, insert new data directly.
 

Note that the table to which data is inserted must have a primary key or a unique index! Otherwise,ReplaceData is inserted directly, which leads to duplicate data in the table.

2)ReplaceAll usage:

1.Replace IntoTbl_name (Col_name,...)Values(...)2.Replace IntoTbl_name (Col_name,...)Select...3.Replace IntoTbl_nameSet Col_name=Value ,...
 

3)Insert into XXX on duplicate key updateUsage:

The statement function is similar to the replace into function. The usage is as follows:
The ID in the table is defined as the unique index or primary key.

 
Insert Into Table(ID, B, c)Values(1,2,3)OnDuplicateKey UpdateC=C+1;
 

If a record with the ID of 1 exists in the table, C = C + 1 is updated. If B is also a unique column, the execution logic is or, id = XX or B = xx

4)Differences between the two statements:

Replace into is more concise and intuitive. It tells us what will happen in this SQL statement at the beginning. The on duplicate key may be ignored in numerous statements...

However, replace into must assign the correct value to each field. Otherwise, the field will be written to the default value. The on duplicate key can flexibly update only some values in the data.

If the table does not have a primary key record, the implementation is the same, but if the table has a primary key record, replace is equivalent to executing the delete and insert operations, the insert * update operation is equivalent to executing the if exist do update else do insert operation.Therefore, if the replace field is incomplete, all unupdated fields will be changed to the default value, the auto-increment ID changes to the latest value (in this way, if the auto-increment ID is used as the flag, the record may be lost );The insert * update operation only updates some fields, but does not change the fields that have not been updated (it is not forced to be changed to the default value ).