If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar 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;
(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)
The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.
With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to when connecting to , the affected-rows value is 1 (not 0) if an existing row is set to its current values.
If column b is also unique, the is equivalent to this statement instead:
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
You can use the function in the clause to refer to column values from the portion of the statement. In other words, in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The function is meaningful only inINSERT ... UPDATE statements and returns NULL otherwise. Example:
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
That statement is identical to the following two statements:
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;
If a table contains an AUTO_INCREMENT column and inserts or updates a row, the function returns the AUTO_INCREMENT value.
The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE.
Because the results of statements depend on the ordering of rows from the and this order cannot always be guaranteed, it is possible when logging statements for the master and the slave to diverge. Thus, statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using MIXED mode. In addition, an statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637) See also .
In MySQL 5.7, an INSERT ... ON DUPLICATE KEY UPDATE on a partitioned table using a storage engine such as that employs table-level locks locks any partitions of the table in which a partitioning key column is updated. (This does not occur with tables using storage engines such as that employ row-level locking.) See , for more information.
转载