MySQL "on DUPLICATE KEY UPDATE" uses

One, insert or update requirements

Requirement: If there is a record in test_table update, the insert does not exist.

Ii. Old Practice: 3 sql     

SELECT * FROM test_table where id = 1;   Query whether the table already has records insert into test_table (id,name) VALUES (1, "may");  Record does not exist, execute insertupdate test_table set name = ' May ' where id = 1; Record exists, perform update

Query first, determine whether the record exists, if not, insert a record, or update the record if one exists. In the case of a small amount of data, there is no efficiency problem, available.

However, once the amount of data is large, the efficiency will be very low, and there will be data concurrency problems, such as: data duplication, or even run into memory overflow. Wait a minute. Using "On duplicate key update" is significantly more efficient in the case of large data volumes. Only one SQL, directly in the database layer to do processing, a lot less judgment in the business layer.

Third, use on duplicate key update:1 SQL

Using the on duplicate key update,1 SQL can take care of the above 3 SQL things, and more efficiently.        

Insert into test_table (id,name) VALUES (1, ' could ') on duplicate key update name = values (name);

"It is important to note that the SQL required for the above on duplicate key update requires an ID as the primary key, and the primary key value is automatically increased in the table"

MySQL "on DUPLICATE KEY UPDATE" uses