INSERT ... ON DUPLICATE KEY UPDATE Statement
If you specify an ON DUPLICATE KEY UPDATE
clause and a row to be inserted would cause a duplicate value in a UNIQUE
index or PRIMARY KEY
, an UPDATE
of the old row occurs. For example, if column a
is declared as UNIQUE
and contains the value 1
, the following two statements have similar effect:
If column b
is also unique, the INSERT
is equivalent to this UPDATE
statement instead:
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.
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 a table contains an AUTO_INCREMENT
column and INSERT ... ON DUPLICATE KEY UPDATE
inserts or updates a row, the LAST_INSERT_ID()
function returns the AUTO_INCREMENT
value.
The ON DUPLICATE KEY UPDATE
clause can contain multiple column assignments, separated by commas.
In assignment value expressions in the ON DUPLICATE KEY UPDATE
clause, you can use the VALUES(
col_name
)
function to refer to column values from the INSERT
portion of the INSERT ... ON DUPLICATE KEY UPDATE
statement. In other words, VALUES(
col_name
)
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 VALUES()
function is meaningful only in the ON DUPLICATE KEY UPDATE
clause or INSERT
statements and returns NULL
otherwise. Example:
That statement is identical to the following two statements:
Important
This usage is deprecated in MySQL 8.0.20, and is subject to removal in a future release of MySQL. Use a row alias, or row and column aliases, instead.
Beginning with MySQL 8.0.19, it is possible to use an alias for the row, with, optionally, one or more of its columns to be inserted, following the VALUES
or SET
clause, and preceded by the AS
keyword. Using the row alias new
, the statement shown previously using VALUES()
to access the new column values can be written in the form shown here:
If, in addition, you use the column aliases m
, n
, and p
, you can omit the row alias in the assignment clause and write the same statement like this:
When using column aliases in this fashion, you must still use a row alias following the VALUES
clause, even if you do not make direct use of it in the assignment clause.
最后更新于