Mysql on DUPLICATE KEY update usage

Using on DUPLICATE KEY update

Look at two SQL first

The code is as follows	Copy Code
INSERT into Osc_visit_stats (stat_date,type,id,view_count) VALUES (?,?,?,?) On DUPLICATE KEY UPDATE view_count=view_count+?

Osc_visit_stats table has composite primary key (Stat_date,type,id)

Multi-field Update

The code is as follows	Copy Code
INSERT into Osc_space_visit_records (space,user,visit_count,ip,visit_time) VALUES (?,?,?,?,?) On DUPLICATE KEY UPDATE Visit_count=visit_count+1,ip=?,visit_time=now ()

If you specify on DUPLICATE key update and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:

The code is as follows	Copy Code
mysql> INSERT into table (a,b,c) VALUES (1,2,3) -> on DUPLICATE KEY UPDATE c=c+1; mysql> 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.

Note: If column B is also a unique column, insert is equivalent to this UPDATE statement:

The code is as follows	Copy Code
mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If the a=1 OR b=2 matches more than one row, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on tables with multiple unique keywords.

You can use the values (col_name) function in the UPDATE clause from the INSERT ... The insert portion of the UPDATE statement references the column value. In other words, if no duplicate keyword conflict occurs, values (col_name) in the update clause can reference the value of the inserted col_name. This function is especially useful for multiple rows of inserts. The VALUES () function is only in the insert ... is meaningful in the UPDATE statement and returns null at other times.

Example:

The code is as follows	Copy Code
mysql> INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6) -> on DUPLICATE KEY UPDATE c=values (a) +values (b);

This statement acts the same as the following two statements:

The code is as follows	Copy Code
mysql> INSERT into table (a,b,c) VALUES (1,2,3) -> on DUPLICATE KEY UPDATE c=3; mysql> INSERT into table (a,b,c) VALUES (4,5,6) -> on DUPLICATE KEY UPDATE c=9;

When you use on DUPLICATE KEY update, the delayed option is ignored.

Example :

The code is as follows	Copy Code
mysql> INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6) on DUPLICATE KEY UPDATE c=values (a) +values (b);

This statement acts the same as the following two statements:

The code is as follows	Copy Code
mysql> INSERT into table (a,b,c) VALUES (4,5,6) on DUPLICATE KEY UPDATE c=9;

When you use on DUPLICATE KEY update, the delayed option is ignored.

Summary: Delayed as a quick insert, is not very concerned about the failure of, improve the insertion performance.

Ignore only focus on primary key corresponding record is not present, added without, and ignored.

On DUPLICATE Key UPDATE when adding, focus on non primary key columns, pay attention to the difference with ignore. The specified column is updated and none is added.