Если вы укажете предложение ON DUPLICATE KEY UPDATE и строка, подлежащая вставке, вызовет дублирование значения в индексе UNIQUE или PRIMARY KEY, произойдёт обновление старой строки с помощью оператора UPDATE. Например, если столбец a объявлен как UNIQUE и содержит значение 1, следующие два оператора имеют сходный эффект:

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;

Эффекты не совсем идентичны: для таблицы InnoDB, где a является столбцом с автоматическим увеличением, оператор INSERT увеличивает значение автоматического увеличения, а оператор UPDATE — нет.

Если столбец b также уникален, оператор INSERT эквивалентен следующему оператору UPDATE:

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

Если a=1 OR b=2 соответствует нескольким строкам, обновляется только одна строка. В целом следует избегать использования предложения ON DUPLICATE KEY UPDATE в таблицах с несколькими уникальными индексами.

При использовании ON DUPLICATE KEY UPDATE значение количества затронутых строк на строку равно 1, если строка вставляется как новая строка, 2, если существующая строка обновляется, и 0, если существующая строка устанавливается в её текущие значения. Если вы указываете флаг CLIENT_FOUND_ROWS в функции C API при подключении к mysqld, значение количества затронутых строк равно 1 (а не 0), если существующая строка устанавливается в её текущие значения.

Если таблица содержит столбец AUTO_INCREMENT и оператор INSERT ... ON DUPLICATE KEY UPDATE вставляет или обновляет строку, функция LAST_INSERT_ID() возвращает значение AUTO_INCREMENT.

Предложение ON DUPLICATE KEY UPDATE может содержать несколько присваиваний столбцов, разделённых запятыми.

Можно использовать IGNORE с ON DUPLICATE KEY UPDATE в операторе INSERT, но это может вести себя не так, как ожидается, при вставке нескольких строк в таблицу с несколькими уникальными ключами. Это становится очевидным, когда обновлённое значение само по себе является значением дублирующего ключа. Рассмотрим таблицу t, созданную и заполненную операторами, показанными здесь:

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: 0

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

Теперь мы пытаемся вставить две строки, одна из которых содержит значение дублирующего ключа, используя ON DUPLICATE KEY UPDATE, где само предложение UPDATE приводит к нарушению уникальности ключа:

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)

Первая строка содержит дублирующее значение для одного из уникальных ключей таблицы (столбец a), но b=b+1 в предложении UPDATE приводит к нарушению уникальности ключа для столбца b; оператор сразу отклоняется с ошибкой, и ни одна строка не обновляется. Повторим оператор, на этот раз добавив ключевое слово IGNORE, вот так:

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

На этот раз предыдущая ошибка понижена до предупреждения, как показано здесь:

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

Поскольку оператор не был отклонен, выполнение продолжается. Это означает, что вторая строка вставляется в t, как мы видим здесь:

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

В выражениях значений присваивания в предложении ON DUPLICATE KEY UPDATE можно использовать функцию VALUES(col_name) для ссылки на значения столбцов из части INSERT оператора INSERT ... ON DUPLICATE KEY UPDATE. Другими словами, VALUES(col_name) в предложении ON DUPLICATE KEY UPDATE относится к значению col_name, которое должно было быть вставлено, если бы не возникло конфликта дублирования ключа. Эта функция особенно полезна при многострочных вставках. Функция VALUES() имеет смысл только как ввод для списков значений оператора INSERT или в предложении ON DUPLICATE KEY UPDATE оператора INSERT и возвращает NULL в противном случае. Например:

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

Этот оператор идентичен следующим двум операторам:

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;

Можно использовать псевдоним для строки, с необязательными одним или несколькими её столбцами, которые будут вставлены, после предложения VALUES или SET и перед ключевым словом AS. Используя псевдоним строки new, оператор, ранее показанный с использованием VALUES() для доступа к новым значениям столбцов, может быть записан в форме, показанной здесь:

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

Если, кроме того, вы используете столбцевые псевдонимы m, n и p, вы можете опустить псевдоним строки в предложении присваивания и записать тот же оператор так:

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;

При использовании столбцевых псевдонимов таким образом, вы всё равно должны использовать псевдоним строки после предложения VALUES, даже если вы не используете его напрямую в предложении присваивания.

Оператор INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, который использует VALUES() в предложении UPDATE, как этот, вызывает предупреждение:

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

Вы можете устранить такие предупреждения, используя подзапрос вместо этого, например, так:

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

Вы также можете использовать псевдонимы строк и столбцов с предложением SET, как упоминалось ранее. Использование SET вместо VALUES в двух операторах INSERT ... ON DUPLICATE KEY UPDATE, только что показанных, может быть выполнено так:

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;

Псевдоним строки не должен совпадать с именем таблицы. Если столбцевые псевдонимы не используются или если они совпадают с именами столбцов, их необходимо различать, используя псевдоним строки в предложении ON DUPLICATE KEY UPDATE. Столбцевые псевдонимы должны быть уникальными относительно псевдонима строки, к которому они относятся (то есть, столбцевые псевдонимы, относящиеся к столбцам одной строки, не могут совпадать).

Для операторов INSERT ... SELECT эти правила относятся к допустимым формам выражений запросов SELECT, на которые можно ссылаться в предложении ON DUPLICATE KEY UPDATE:

Ссылки на столбцы из UNION не поддерживаются. Чтобы обойти это ограничение, перепишите UNION как производную таблицу, чтобы её строки можно было рассматривать как результат набора из одной таблицы. Например, этот оператор выдаёт ошибку:

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

Вместо этого используйте эквивалентный оператор, который переписывает UNION как производную таблицу:

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

Приём переписывания запроса как производной таблицы также позволяет ссылки на столбцы из запросов GROUP BY.

Поскольку результаты операторов INSERT ... SELECT зависят от порядка строк из SELECT, а этот порядок не всегда гарантируется, то при ведении журнала операторов INSERT ... SELECT ON DUPLICATE KEY UPDATE для источника и реплики может быть расхождение. Таким образом, операторы INSERT ... SELECT ON DUPLICATE KEY UPDATE помечаются как небезопасные для репликации на основе операторов. Такие операторы генерируют предупреждение в журнале ошибок при использовании режима на основе операторов и записываются в двоичный журнал с использованием формата на основе строк при использовании режима MIXED. Оператор INSERT ... ON DUPLICATE KEY UPDATE в отношении таблицы, имеющей более одного уникального или первичного ключа, также помечается как небезопасный. (Ошибка #11765650, Ошибка #58637)

См. также Раздел 19.2.1.1, «Преимущества и недостатки репликации на основе операторов и на основе строк».