MySQL INSERT INTO ... On DUPLICATE KEY Update usage

MySQL has been supporting inserts since version 4.1 ... On DUPLICATE KEY UPDATE syntax, which requires 3 SQL statements (select,insert,update) to be executed, and reduced to 1 statements to complete.

For example, the Ipstats table structure is as follows:

The code is as follows	Copy Code
CREATE TABLE Ipstats ( IP VARCHAR not NULL UNIQUE, Clicks SMALLINT (5) UNSIGNED not NULL DEFAULT ' 0 ' );

3 SQL statements would have been executed, as follows:

The code is as follows	Copy Code
IF (SELECT * from ipstats WHERE ip= ' 192.168.0.1 ') { UPDATE ipstats SET clicks=clicks+1 WHERE ip= ' 192.168.0.1 '; } else { INSERT into Ipstats (IP, clicks) VALUES (' 192.168.0.1 ', 1); }

And now you can do this with just 1 SQL statements:

The code is as follows	Copy Code
INSERT into Ipstats VALUES (' 192.168.0.1 ', 1) on DUPLICATE KEY UPDATE clicks=clicks+1;

Note that to use this statement, the condition is that the table must have a unique index or primary key.

Summarized as follows:

1. If a primary key record does not exist in the table, replace and insert*update are the same characteristics as insert.
2. If a primary key record exists in the table, replace is equivalent to performing a delete and insert two operation, whereas insert*update is equivalent to executing the IF exist do update else does insert. Therefore, if the replace fill has incomplete fields, the field that is not updated will be modified to the default value, and if there is an ID, the ID will change to the most recent value (so that the record may be lost if it is marked with a self ID); insert* Update only updates some of the fields and does not change for fields that are not updated (the default values are not enforced).

More than one record operation:

code is as follows	copy code
insert into t (a , b,c) VALUES (' A1 ', ' B1 ', ' C1 '), (' A2 ', ' B2 ', ' C2 ') on duplicate key update t.c=values (T.C)