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 INSERT statement, and the insertion of a row results in a duplicate value in an UNIQUE index or PRIMARY KEY, UPDATE is executed on the row where the duplicate value occurs. Insert a new row if it does not cause the problem of duplicate columns with only 1 value.
For example, if the column a is a primary key or has an UNIQUE index and contains a value of 1, the following two statements have the same effect:

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 displays 1; If the original record is updated, the value of the affected row is displayed at 2.
This syntax can also be used like this:
If the INSERT multi-row record (assuming that a is a primary key or a is an UNIQUE indexed column):

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 (article 2 is repeated with article 1, and c is +1 on the original value).

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 (article 2 is repeated with article 1, and c directly takes the repeated value of 7).
Note: ON DUPLICATE KEY UPDATE is only a special syntax for MySQL, not SQL standard syntax!
This syntax and is suitable for situations where you need to determine whether a record exists, or if it does not, insert it and update it.

INSERT INTO.. ON DUPLICATE KEY updates multi-line records
If ON DUPLICATE KEY UPDATE is specified at the end of the INSERT statement, and the insertion of a row results in a duplicate value in an UNIQUE index or PRIMARY KEY, the old row UPDATE is executed; Insert a new row if it does not cause the problem of duplicate columns with only 1 value. For example, if the 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;
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; If the original record is updated, the value of the affected row is 2.
If you want to know more about INSERT INTO.. For the function description of ON DUPLICATE KEY, see MySQL reference documentation: 13.2.4.INSERT syntax

Now the question is, if INSERT records multiple lines, what is the value of the field after ON DUPLICATE KEY UPDATE? To know that there can only be 1 ON DUPLICATE KEY UPDATE in a single INSERT statement, will he update the record by 1 line or will he update all the lines that need to be updated? This problem has been bothering me for a long time, but actually using the VALUES() function 1 tangent has been solved.

For example, the field a is defined as UNIQUE, and records (2,2,9) and (3,2,1) already exist in the original database table table. If the a value of the inserted record is the same as the original record, the original record will be updated; otherwise, a new row will be inserted:

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

When the above SQL statement is executed, it is found that the a in (2,5,7) has only 1 value conflict with the original record (2,2,9), then ON DUPLICATE KEY UPDATE is executed, the original record (2,2,9) is updated to (2,5,9), and the original record (3,2,1) is updated to (3,3,1), and the new records (1,2,3) and (4,8,2) are inserted.
Note: ON DUPLICATE KEY UPDATE is only a special syntax for MySQL, not SQL standard syntax!