MySQL replace and on duplicate key update usage and functions

Recently, this problem often occurs during project development: You want to insert a piece of data, but you want to update the data if the data number already exists in the data table.

 

In this case, I first query and then use Program Determine whether there are results. If yes, update the data. If no, insert new data.

But you will find that this will make you feel Code So I gave up this method.

Because I am using a MySQL database and I am not using the merger method in Oracle, I went online to Google and flipped through the manual. I finally got a result, that is

 

Insert... on duplicate key update statement.

 

Insert... on duplicate key update: when inserting data, if the value of the primary key or unique index corresponding to the inserted data already exists in the table, modify the field value corresponding to the data. If it does not exist, insert it directly.

 

Insert... on duplicate key update usage:

 

Insert into Test2 select * From test1 on duplicate key update a = 'remove-Me ';

 

In this way, you only need one SQL statement to meet the requirements just now.

 

At the same time, I found another Syntax: replace

 

Replace: when inserting data, if the value of the corresponding primary key or unique index in the inserted data already exists in the table, delete the value in the table and insert it. Insert the data directly if it does not exist.

 

Replace usage:

Replace [low_priority | delayed] [into] tbl_name [(col_name,...)]

 

{Values | value} ({expr | default },...),(...),...

 

Or:

 

Replace [low_priority | delayed] [into] tbl_name set col_name = {expr | default },...

 

Or:

 

Replace [low_priority | delayed] [into] tbl_name [(col_name,...)] select...

 

Example: replace into table_name select * From table_name;

 

This statement can be used for table data replication or other appropriate operations.