Mysql:onduplicatekeyupdate usage

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:
The original 3 SQL statements were executed as follows:
The code is as follows:

IF (SELECT * from t_table WHERE id=1001 ') {UPDATE t_table SET cnt=cnt+1 WHERE id=1001;} else {INSERT into ipstats (ID, CNT) VALUES (1001, 1);}

Now, the code looks like this:

INSERT INTO t_table (id,cnt) VALUES (1001,2) on duplicate key update cnt=cnt+1;
Update t_table set cnt=cnt+1 WHERE id=1001;

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.

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:

The code is as follows:

INSERT into TABLE (a,b,c) VALUES (All-in-all), (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!