ONDUPLICATEKEYUPDATE in MySQL

Today, I used MySQL's ON DUPLICATE KEY UPDATE to determine whether to insert data. Now Mark the following!

If you want to insert or UPDATE data without data in the database, you can choose on duplicate key update.

On duplicate key update can be used to UPDATE the old row when the UNIQUE index or primary key exists.

For example, if column a is defined as UNIQUE and contains a value of 1, the following two statements have the same effect:

Insert into table (a, B, c) VALUES (1, 2, 3) on duplicate key update c = c + 1, B = B-1;

UPDATE table SET c = c + 1, B = B-1 WHERE a = 1;

For example, if you INSERT multiple rows of records (assuming a is the primary key or a is a UNIQUE index column ):

Insert into table (a, c) VALUES (1, 3), (1, 7) on duplicate key update c = c + 1;

After execution, the value of c is changed to 4 (the second repeat with the first one, and c is on the original value + 1 ).

Insert into table (a, c) VALUES (1, 3), (1, 7) on duplicate key update c = VALUES (c );

After execution, the value of c will change to 7 (the second repeat with the first repeat, and c will directly get the repeated value 7 ).

Note: on duplicate key update is only a special MySQL syntax, not a standard SQL syntax!