ВСТАВКА С ОБНОВЛЕНИЕМ ПРИ ДВОЙНОМ КЛЮЧЕ

Синтаксис

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
  {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
  [ ON DUPLICATE KEY UPDATE
    col=expr
      [, col=expr] ... ]

Или:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

Или:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

Описание

INSERT ... ON DUPLICATE KEY UPDATE — это расширение MariaDB/MySQL для оператора ВСТАВКИ, которое, если обнаружит дублированный уникальный или первичный ключ, выполнит вместо этого обновление ОБНОВЛЕНИЯ.

Значение количества затронутых строк сообщается как 1, если строка вставлена, и 2, если строка обновлена, если флаг API CLIENT_FOUND_ROWS не установлен.

Если совпадает более одного уникального индекса, обновляется только первый. Не рекомендуется использовать этот оператор для таблиц с более чем одним уникальным индексом.

Если таблица имеет первичный ключ AUTO_INCREMENT, а оператор вставляет или обновляет строку, функция LAST_INSERT_ID() возвращает значение AUTO_INCREMENT.

Функция VALUES() может использоваться только в ON DUPLICATE KEY UPDATE-клаузуле и не имеет смысла в любом другом контексте. Она возвращает значения столбцов из INSERT части оператора. Эта функция особенно полезна для многострочных вставок.

Опции IGNORE и DELAYED игнорируются при использовании ON DUPLICATE KEY UPDATE.

Подробности о клаузе PARTITION см. в разделе Обрезка и выборка раздела.

Этот оператор активирует триггеры INSERT и UPDATE. Подробности см. в разделе Обзор триггеров.

См. также похожий оператор REPLACE.

Примеры

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

Если существующего ключа нет, оператор выполняется как обычная ВСТАВКА:

INSERT INTO ins_duplicate VALUES (4,'Gorilla') 
  ON DUPLICATE KEY UPDATE animal='Gorilla';
Query OK, 1 row affected (0.07 sec)

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Aardvark |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

Обычная ВСТАВКА со значением первичного ключа 1 завершится ошибкой из-за существующего ключа:

INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Однако мы можем использовать оператор ВСТАВКА С ОБНОВЛЕНИЕМ ПРИ ДВОЙНОМ КЛЮЧЕ вместо него:

INSERT INTO ins_duplicate VALUES (1,'Antelope') 
  ON DUPLICATE KEY UPDATE animal='Antelope';
Query OK, 2 rows affected (0.09 sec)

Обратите внимание, что затронуты две строки, но это относится только к ОБНОВЛЕНИЮ.

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

Добавление второго уникального столбца:

ALTER TABLE ins_duplicate ADD id2 INT;
UPDATE ins_duplicate SET id2=id+10;
ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);

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

INSERT INTO ins_duplicate VALUES (2,'Lion',13) 
  ON DUPLICATE KEY UPDATE animal='Lion';
Query OK, 2 rows affected (0.004 sec)

SELECT * FROM ins_duplicate;
+----+----------+------+
| id | animal   | id2  |
+----+----------+------+
|  1 | Antelope |   11 |
|  2 | Lion     |   12 |
|  3 | Zebra    |   13 |
|  4 | Gorilla  |   14 |
+----+----------+------+

Хотя третья строка с id 3 имеет id2 13, которая также совпадала, она не была обновлена.

Изменение id на поле auto_increment. Если новая строка добавлена, auto_increment переходит вперед. Если строка обновляется, она остается прежней.

ALTER TABLE `ins_duplicate` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE ins_duplicate DROP id2;
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              5 |
+----------------+

INSERT INTO ins_duplicate VALUES (2,'Leopard') 
  ON DUPLICATE KEY UPDATE animal='Leopard';
Query OK, 2 rows affected (0.00 sec)

SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              5 |
+----------------+

INSERT INTO ins_duplicate VALUES (5,'Wild Dog') 
  ON DUPLICATE KEY UPDATE animal='Wild Dog';
Query OK, 1 row affected (0.09 sec)

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Leopard  |
|  3 | Zebra    |
|  4 | Gorilla  |
|  5 | Wild Dog |
+----+----------+

SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              6 |
+----------------+

Ссылка на значения столбцов из части ВСТАВКИ оператора:

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

См. функцию VALUES() для получения дополнительной информации.

См. также

• ВСТАВКА
• ВСТАВКА DELAYED
• ВСТАВКА ВЫБОР
• HIGH_PRIORITY и LOW_PRIORITY
• Одновременные вставки
• ВСТАВКА - Значения по умолчанию и дубликаты
• ВСТАВКА IGNORE
• VALUES()