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

MySQL "on DUPLICATE KEY UPDATE" syntax
if 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 update is performed on the row where the duplicate value occurs ; Insert a new row if the problem does not cause a unique value column to repeat.
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 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 existing record is updated, the value of the affected row displays 2.
This syntax can also be used in this way:
If you insert a multiline record (assuming a is a primary key or a is a unique indexed column):

Copy 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 changes to 4 (the second one repeats with the first, and C is +1 on the original value).

Copy 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 one repeats with the first, and C takes a duplicate value of 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 situations where you need to determine whether a record exists, and if it does not exist, then the scene that is updated is inserted.

INSERT into.. On DUPLICATE key to update multiple-line records
if on DUPLICATE KEY UPDATE is specified at the end of the INSERT statement, and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed, and if the problem does not cause the unique value column to repeat, 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 Code code as follows:


INSERT into TABLE (a,b,c)


VALUES (1,2,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 1, and if the existing record is updated, the value of the affected row is 2.
If you want to learn more about insert INTO. On DUPLICATE Key's function description, see MySQL Reference document: 13.2.4. Insert Syntax

Now the question is, how do you specify the value of the field after DUPLICATE KEY Update if you insert a multiline record? To know that there can be only one on DUPLICATE KEY update in an INSERT statement, whether he updates a row of records or updates all the rows that need to be updated. This problem has plagued me for a long time, in fact, using the values () function all the problems are solved.

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

Copy Code code as follows:


INSERT into TABLE (a,b,c) VALUES


(1,2,3),


(2,5,7),


(3,3,6),


(4,8,2)


on DUPLICATE KEY UPDATE b=values (b);

Execution of the above SQL statement discovers a unique value conflict with the original record (2,2,9) in (2,5,7), executes on DUPLICATE KEY update, updates the original record (2,2,9) to (2,5,9), Updates (3,2,1) to (3,3,1), Insert new Record (1,2,3) and (4,8,2)
Note: On DUPLICATE KEY update is only a unique syntax for MySQL, not SQL standard syntax!