MysqlONDUPLICATEKEYUPDATE statement example _ MySQL

MysqlONDUPLICATEKEYUPDATE statement example bitsCN. comMySQL supports INSERT... The on duplicate key update syntax allows you to execute three SQL statements (SELECT, INSERT, UPDATE) and reduce them to one statement.
For example, the ipstats table structure is as follows:

Create table ipstats (
Ip VARCHAR (15) not null unique,
Clicks SMALLINT (5) unsigned not null default '0'
);

Originally, three SQL statements were required:

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 );
}

Now, you only need to run the following SQL statement:

Insert into ipstats VALUES ('192. 168.0.1 ', 1) on duplicate key update clicks = clicks + 1;

Note: To use this statement, the table must have a unique index or primary key.
Summary:
1. if there is no primary key record in the table, replace and insert * update both have the same features as insert.
2. if a primary key record exists in the table, replace is equivalent to executing the delete and insert operations, while 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 (if the auto-increment id is used as the flag, the record may be lost), while insert * update only updates some fields, fields that have not been updated will not change (they will not be forcibly changed to the default value ).
Multiple record operations:

Insert into t (a, B, c) values ('A1', 'B1', 'C1'), ('A2 ', 'B2', 'C2 ')
On duplicate key update t. c = values (t. c)
BitsCN.com