In-depth analysis of MySQL "on DUPLICATE KEY UPDATE" syntax

MySQL "on DUPLICATE KEY UPDATE" syntax
If an on DUPLICATE KEY UPDATE is specified at the end of the INSERT statement, and the row is inserted to cause duplicate values in a unique index or primary KEY, UPDATE is performed on the row where the duplicate value occurs, and if the unique value column is not duplicated. The new row is inserted.
For example, if column A is a primary key or has a unique index and contains a value of 1, the following two statements have the same effect:

Copy the code code as follows:
INSERT into TABLE (a,c) VALUES (1,3) on DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

If the row is inserted as a new record, the value of the affected row is displayed as 1, and if the original record is updated, the value of the affected row is displayed as 2.
This syntax can also be used in this way:
If you insert multiple rows of records (assuming A is a primary key or a is a unique indexed column):

Copy the code code as follows:
INSERT into TABLE (a,c) VALUES (1,3), (1,7) on DUPLICATE KEY UPDATE c=c+1;

After execution, the value of C becomes 4 (the second is repeated with the first one, and C is +1 on the original value).

Copy the code code as follows:
INSERT into TABLE (a,c) VALUES (1,3), (1,7) on DUPLICATE KEY UPDATE c=values (c);

After execution, the value of C changes to 7 (the second is repeated with the first one, and C is the value of the duplicate 7 directly).
Note: On DUPLICATE KEY update is only a unique syntax for MySQL, not SQL standard syntax!
This syntax and suitability is used in scenarios where it is necessary to determine whether a record exists or not, and the insertion exists.

INSERT into.. On DUPLICATE key updates multiple rows of records
If an on DUPLICATE KEY UPDATE is specified at the end of the INSERT statement, and the row is inserted causing duplicate values in a unique index or primary KEY, the old row UPDATE is executed, and if the unique value column is not duplicated, the new row is inserted. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:

Copy the code code as follows:
INSERT into TABLE (a,b,c)
VALUES (DUPLICATE) on the KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

If the row is inserted as a new record, the value of the affected row is 1, and if the original record is updated, the value of the affected row is 2.
If you want to learn more about insert INTO. On DUPLICATE key function description, see MySQL Reference document: 13.2.4. Insert Syntax

Now the question is, if the insert multiline record, on DUPLICATE KEY update after the field value how to specify? Be aware that there can be only one on DUPLICATE KEY update in an INSERT statement, whether he will update a row of records or update all rows that need to be updated. This problem has plagued me for a long time, in fact, the use of the values () function all solve the problem.

For example, field A is defined as unique, and records (2,2,9) and (3,2,1) exist in the original database table tables, and if the a value of the inserted record is duplicated with the original record, the original record is updated or the new row is inserted:

Copy the code code as follows:
INSERT into TABLE (a,b,c) VALUES
(a),
(2,5,7),
(3,3,6),
(4,8,2)
On DUPLICATE KEY UPDATE b=values (b);

In the execution of the above SQL statement, Discovery (2,5,7) has a unique value conflict with the original record (2,2,9), execute on DUPLICATE KEY update, update the original record (2,2,9) to (2,5,9), update (3,2,1) to (3,3,1), Inserting new records (4,8,2) and
Note: On DUPLICATE KEY update is only a unique syntax for MySQL, not SQL standard syntax!

In-depth analysis of MySQL "on DUPLICATE KEY UPDATE" syntax