Если вы укажете предложение ON DUPLICATE KEY UPDATE и строка, которую нужно вставить, вызовет дублированное значение в индексе UNIQUE или PRIMARY KEY, произойдет обновление старой строки. Например, если столбец 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 (1,1), (2,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

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

mysql> INSERT INTO t VALUES (2,3), (3,3) ON DUPLICATE KEY UPDATE a=a+1, b=b-1;
ERROR 1062 (23000): Duplicate entry '1' for key 't.b'
mysql> SELECT * FROM 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 (2,3), (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> SELECT * FROM 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;

Для операторов 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)

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

Операции INSERT ... ON DUPLICATE KEY UPDATE для разнесённой таблицы, использующей движок хранения, например, MyISAM, который использует блокировки на уровне таблицы, блокирует любые разделы таблицы, в которых обновляется столбец ключа разбиения. (Это не происходит с таблицами, использующими движки хранения, например, InnoDB, которые используют блокировки на уровне строк.) Более подробную информацию см. в Разделе 22.6.4, «Разбиение и блокировка».