Movatterモバイル変換

If you specify anON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in aUNIQUE index orPRIMARY KEY, anUPDATE of the old row occurs. For example, if columna is declared asUNIQUE and contains the value1, the following two statements have similar effect:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=c+1;UPDATE t1 SET c=c+1 WHERE a=1;

The effects are not quite identical: For anInnoDB table wherea is an auto-increment column, theINSERT statement increases the auto-increment value but theUPDATE does not.

If columnb is also unique, theINSERT is equivalent to thisUPDATE statement instead:

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

Ifa=1 OR b=2 matches several rows, onlyone row is updated. In general, you should try to avoid using anON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

WithON 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 theCLIENT_FOUND_ROWS flag to themysql_real_connect() C API function when connecting tomysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

If a table contains anAUTO_INCREMENT column andINSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, theLAST_INSERT_ID() function returns theAUTO_INCREMENT value.

TheON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.

It is possible to useIGNORE withON DUPLICATE KEY UPDATE in anINSERT statement, but this may not behave as you expect when inserting multiple rows into a table that has multiple unique keys. This becomes apparent when an updated value is itself a duplicate key value. Consider the tablet, created and populated by the statements shown here:

mysql> CREATE TABLE t (a SERIAL, b BIGINT NOT NULL, UNIQUE KEY (b));;Query OK, 0 rows affected (0.03 sec)mysql> INSERT INTO t VALUES ROW(1,1), ROW(2,2);Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> TABLE t;+---+---+| a | b |+---+---+| 1 | 1 || 2 | 2 |+---+---+2 rows in set (0.00 sec)

Now we attempt to insert two rows, one of which contains a duplicate key value, usingON DUPLICATE KEY UPDATE, where theUPDATE clause itself results in a duplicate key value:

mysql> INSERT INTO t VALUES ROW(2,3), ROW(3,3) ON DUPLICATE KEY UPDATE a=a+1, b=b-1;ERROR 1062 (23000): Duplicate entry '1' for key 't.b'mysql> TABLE t;+---+---+| a | b |+---+---+| 1 | 1 || 2 | 2 |+---+---+2 rows in set (0.00 sec)

The first row contains a duplicate value for one of the table's unique keys (columna), butb=b+1 in theUPDATE clause results in a unique key violation for columnb; the statement is immediately rejected with an error, and no rows are updated. Let us repeat the statement, this time adding theIGNORE keyword, like this:

mysql> INSERT IGNORE INTO t VALUES ROW(2,3), ROW(3,3)     -> ON DUPLICATE KEY UPDATE a=a+1, b=b-1;Query OK, 1 row affected, 1 warning (0.00 sec)Records: 2  Duplicates: 1  Warnings: 1

This time, the previous error is demoted to a warning, as shown here:

mysql> SHOW WARNINGS;+---------+------+-----------------------------------+| Level   | Code | Message                           |+---------+------+-----------------------------------+| Warning | 1062 | Duplicate entry '1' for key 't.b' |+---------+------+-----------------------------------+1 row in set (0.00 sec)

Because the statement was not rejected, execution continues. This means that the second row is inserted intot, as we can see here:

mysql> TABLE t;+---+---+| a | b |+---+---+| 1 | 1 || 2 | 2 || 3 | 3 |+---+---+3 rows in set (0.00 sec)

In assignment value expressions in theON DUPLICATE KEY UPDATE clause, you can use theVALUES(col_name) function to refer to column values from theINSERT portion of theINSERT ... ON DUPLICATE KEY UPDATE statement. In other words,VALUES(col_name) in theON DUPLICATE KEY UPDATE clause refers to the value ofcol_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. TheVALUES() function is meaningful only as an introducer forINSERT statement value lists, or in theON DUPLICATE KEY UPDATE clause of anINSERT statement, and returnsNULL otherwise. For example:

INSERT INTO t1 (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 t1 (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=3;INSERT INTO t1 (a,b,c) VALUES (4,5,6)  ON DUPLICATE KEY UPDATE c=9;

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 theVALUES orSET clause, and preceded by theAS keyword. Using the row aliasnew, the statement shown previously usingVALUES() to access the new column values can be written in the form shown here:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new  ON DUPLICATE KEY UPDATE c = new.a+new.b;

If, in addition, you use the column aliasesm,n, andp, you can omit the row alias in the assignment clause and write the same statement like this:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)  ON DUPLICATE KEY UPDATE c = m+n;

When using column aliases in this fashion, you must still use a row alias following theVALUES clause, even if you do not make direct use of it in the assignment clause.

Beginning with MySQL 8.0.20, anINSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement that usesVALUES() in theUPDATE clause, like this one, throws a warning:

INSERT INTO t1  SELECT c, c+d FROM t2  ON DUPLICATE KEY UPDATE b = VALUES(b);

You can eliminate such warnings by using a subquery instead, like this:

INSERT INTO t1  SELECT * FROM (SELECT c, c+d AS e FROM t2) AS dt  ON DUPLICATE KEY UPDATE b = e;

You can also use row and column aliases with aSET clause, as mentioned previously. EmployingSET instead ofVALUES in the twoINSERT ... ON DUPLICATE KEY UPDATE statements just shown can be done as shown here:

INSERT INTO t1 SET a=1,b=2,c=3 AS new  ON DUPLICATE KEY UPDATE c = new.a+new.b;INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)  ON DUPLICATE KEY UPDATE c = m+n;

The row alias must not be the same as the name of the table. If column aliases are not used, or if they are the same as the column names, they must be distinguished using the row alias in theON DUPLICATE KEY UPDATE clause. Column aliases must be unique with regard to the row alias to which they apply (that is, no column aliases referring to columns of the same row may be the same).

ForINSERT ... SELECT statements, these rules apply regarding acceptable forms ofSELECT query expressions that you can refer to in anON DUPLICATE KEY UPDATE clause:

References to columns from aUNION are not supported. To work around this restriction, rewrite theUNION as a derived table so that its rows can be treated as a single-table result set. For example, this statement produces an error:

INSERT INTO t1 (a, b)  SELECT c, d FROM t2  UNION  SELECT e, f FROM t3ON DUPLICATE KEY UPDATE b = b + c;

Instead, use an equivalent statement that rewrites theUNION as a derived table:

INSERT INTO t1 (a, b)SELECT * FROM  (SELECT c, d FROM t2   UNION   SELECT e, f FROM t3) AS dtON DUPLICATE KEY UPDATE b = b + c;

The technique of rewriting a query as a derived table also enables references to columns fromGROUP BY queries.

Because the results ofINSERT ... SELECT statements depend on the ordering of rows from theSELECT and this order cannot always be guaranteed, it is possible when loggingINSERT ... SELECT ON DUPLICATE KEY UPDATE statements for the source and the replica to diverge. Thus,INSERT ... SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when usingMIXED mode. AnINSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

See alsoSection 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.