Раздел 34 · MySQL с нуля

INSERT ... ON DUPLICATE KEY UPDATE

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

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

Счётчик у входа в зал

Представь контролёра со списком у двери. Пришёл зритель: контролёр ищет его фамилию. Нет в списке, значит дописывает новую строку «Иванов, 1». Есть, значит ставит ещё одну палочку. Одно действие, «отметь человека», и контролёр сам решает, писать строку или палочку.

Наивный способ в базе состоит из двух шагов: SELECT, чтобы узнать, есть ли строка, потом INSERT или UPDATE. Беда в том, что между шагами может вклиниться другой запрос. Два посетителя открыли страницу одновременно, оба увидели «строки нет», оба вставляют, и второй получает ошибку. Нужна одна команда, которая проверит и запишет сразу. Такую операцию называют upsert.

Ключ решает, что такое «уже есть»

Заведём таблицу счётчиков. Первичный ключ здесь film_id: на фильм одна строка.

CREATE TABLE film_views (
  film_id INT PRIMARY KEY,
  views INT NOT NULL DEFAULT 0,
  last_seen DATETIME
);
Query OK, 0 rows affected (0.00 sec)

Первый просмотр «Брата» вставляется без вопросов:

INSERT INTO film_views (film_id, views, last_seen) VALUES (1, 1, NOW());
Query OK, 1 row affected (0.00 sec)

Второй такой же INSERT падает: строка с ключом 1 уже есть.

INSERT INTO film_views (film_id, views, last_seen) VALUES (1, 1, NOW());
ERROR 1062 (23000): Duplicate entry '1' for key 'film_views.PRIMARY'

ON DUPLICATE KEY UPDATE

Допишем к INSERT хвост: что делать, если строка с таким ключом уже есть.

INSERT INTO film_views (film_id, views, last_seen) VALUES (1, 1, NOW())
ON DUPLICATE KEY UPDATE views = views + 1;
Query OK, 2 rows affected (0.00 sec)

Конфликт по первичному ключу превратился в UPDATE существующей строки. Справа от знака равенства views значит текущее значение в таблице. Та же команда для фильма 2, у которого строки ещё нет:

INSERT INTO film_views (film_id, views, last_seen) VALUES (2, 1, NOW())
ON DUPLICATE KEY UPDATE views = views + 1;
Query OK, 1 row affected (0.00 sec)
SELECT * FROM film_views;
+---------+-------+---------------------+
| film_id | views | last_seen           |
+---------+-------+---------------------+
|       1 |     2 | 2026-10-24 12:00:00 |
|       2 |     1 | 2026-10-24 12:00:00 |
+---------+-------+---------------------+
2 rows in set (0.00 sec)

Конфликт ищется по любому уникальному ключу таблицы: по первичному и по каждому UNIQUE. Если уникальных ключей несколько, строка может столкнуться с разными строками по разным ключам. Тогда MySQL обновит одну из них, и какую, предсказать трудно. Держи в таблице для upsert один уникальный ключ, по которому и ищется конфликт.

Что значат 1, 2 и 0 rows affected

Посмотри на ответы ещё раз. Новая строка дала 1 row affected, обновление существующей дало 2 rows affected. Это не две строки, а договорённость: 1 значит «вставил», 2 значит «обновил». Есть и третий вариант:

INSERT INTO film_views (film_id, views) VALUES (1, 1)
ON DUPLICATE KEY UPDATE views = views;
Query OK, 0 rows affected (0.00 sec)

Строка нашлась, но значение не поменялось: 0. По этому числу программа понимает, что именно произошло, не делая лишний SELECT.

Псевдоним строки вместо VALUES()

Пусть просмотры приходят пачкой: сервис раз в минуту присылает, сколько раз открывали каждый фильм. Прибавлять надо не единицу, а пришедшее число. До него можно добраться через псевдоним строки AS new:

INSERT INTO film_views (film_id, views) VALUES (1, 5), (2, 3), (3, 1) AS new
ON DUPLICATE KEY UPDATE views = film_views.views + new.views;
Query OK, 5 rows affected (0.00 sec)
Records: 3  Duplicates: 2  Warnings: 0

film_views.views это то, что уже лежит в таблице, new.views это то, что пришло. Строка Records: 3 Duplicates: 2 говорит: пришло три строки, две из них столкнулись с существующими. А 5 rows affected складывается по договорённости: 2 + 2 за обновления и 1 за вставку.

SELECT * FROM film_views;
+---------+-------+---------------------+
| film_id | views | last_seen           |
+---------+-------+---------------------+
|       1 |     7 | 2026-10-24 12:00:00 |
|       2 |     4 | 2026-10-24 12:00:00 |
|       3 |     1 | NULL                |
+---------+-------+---------------------+
3 rows in set (0.00 sec)

В старых статьях вместо псевдонима пишут функцию VALUES(views). В MySQL 8.4 она ещё работает, но с предупреждением:

INSERT INTO film_views (film_id, views) VALUES (2, 10)
ON DUPLICATE KEY UPDATE views = views + VALUES(views);
Query OK, 2 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Предупреждение 1287 значит «устарело и будет удалено». Пиши псевдоним строки.

AUTO_INCREMENT теряет номера

Вторая таблица: бронь мест на сеанс. Номер брони выдаёт AUTO_INCREMENT, а одно место на сеансе может держать только один человек, за это отвечает UNIQUE KEY.

CREATE TABLE seat_holds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  screening_id INT NOT NULL,
  seat VARCHAR(4) NOT NULL,
  holder VARCHAR(30) NOT NULL,
  UNIQUE KEY uq_hold (screening_id, seat)
);

Анна бронирует место C1, потом Борис перехватывает его через upsert, потом Вера бронирует C2:

INSERT INTO seat_holds (screening_id, seat, holder) VALUES (5, 'C1', 'Анна');
Query OK, 1 row affected (0.00 sec)
INSERT INTO seat_holds (screening_id, seat, holder) VALUES (5, 'C1', 'Борис')
ON DUPLICATE KEY UPDATE holder = 'Борис';
Query OK, 2 rows affected (0.00 sec)
INSERT INTO seat_holds (screening_id, seat, holder) VALUES (5, 'C2', 'Вера');
SELECT * FROM seat_holds;
Query OK, 1 row affected (0.00 sec)

+----+--------------+------+------------+
| id | screening_id | seat | holder     |
+----+--------------+------+------------+
|  1 |            5 | C1   | Борис      |
|  3 |            5 | C2   | Вера       |
+----+--------------+------+------------+
2 rows in set (0.00 sec)

У Веры номер 3, а не 2. Upsert Бориса сначала попытался вставить строку, взял для неё номер 2 и только потом наткнулся на конфликт. Номер не вернулся. Это нормально: AUTO_INCREMENT обещает номера без повторов, а не номера без пропусков. Счётчик на частых upsert быстро убегает вперёд, помни об этом, когда выбираешь тип колонки.

INSERT IGNORE: пропустить молча

Иногда при конфликте ничего обновлять не надо, достаточно не упасть. Для этого есть INSERT IGNORE: строки с конфликтом он пропускает, остальные вставляет. Глеб хочет места C2 и C3, но C2 уже у Веры:

INSERT IGNORE INTO seat_holds (screening_id, seat, holder) VALUES (5, 'C2', 'Глеб'), (5, 'C3', 'Глеб');
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

Ошибка превратилась в предупреждение. Его стоит прочитать:

SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1062 | Duplicate entry '5-C2' for key 'seat_holds.uq_hold' |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

IGNORE глушит не только дубли. Он превращает в предупреждения и ошибки строгого режима: слишком длинную строку обрежет, неверную дату заменит нулями. Поэтому INSERT IGNORE берут только там, где пропуск дубля и есть задуманное поведение, и всегда смотрят на Warnings.

REPLACE: удалить и вставить

REPLACE выглядит как INSERT, но при конфликте удаляет старую строку и вставляет новую. Дарья забирает место C3:

REPLACE INTO seat_holds (screening_id, seat, holder) VALUES (5, 'C3', 'Дарья');
Query OK, 2 rows affected (0.00 sec)
SELECT * FROM seat_holds;
+----+--------------+------+------------+
| id | screening_id | seat | holder     |
+----+--------------+------+------------+
|  1 |            5 | C1   | Борис      |
|  3 |            5 | C2   | Вера       |
|  6 |            5 | C3   | Дарья      |
+----+--------------+------+------------+
3 rows in set (0.00 sec)

2 rows affected здесь буквальные: одна строка удалена, одна вставлена. У брони C3 теперь номер 6 вместо 4: это другая строка. Номер 5 пропал ещё раньше, на INSERT IGNORE. Если на бронь 4 где-то ссылались, эти ссылки теперь ведут в пустоту.

С внешними ключами REPLACE ломается совсем. Поменяем число мест в VIP-зале, на который ссылаются сеансы:

REPLACE INTO halls VALUES (3, 'VIP', 14);
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`kino`.`screenings`, CONSTRAINT `screenings_ibfk_2` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`id`))

Чтобы заменить строку, REPLACE должен удалить зал 3, а удалить его не даёт внешний ключ из screenings, как в уроке про внешние ключи. Будь у ключа ON DELETE CASCADE, вышло бы хуже: вместе с залом молча удалились бы его сеансы. Upsert меняет строку на месте и ссылок не трогает:

INSERT INTO halls VALUES (3, 'VIP', 14) AS new ON DUPLICATE KEY UPDATE seats = new.seats;
Query OK, 2 rows affected (0.00 sec)

Правило простое: для «вставь или обнови» бери ON DUPLICATE KEY UPDATE. REPLACE оставь для таблиц, на которые никто не ссылается и где новый id никому не мешает. В PostgreSQL ту же задачу решает INSERT ... ON CONFLICT, сравни в уроке 18 раздела PostgreSQL.

Что запомнить

  • ON DUPLICATE KEY UPDATE вставляет строку или обновляет ту, что уже есть с тем же уникальным ключом.
  • Ответ 1 row affected значит вставку, 2 значит обновление, 0 значит, что менять было нечего.
  • Псевдоним строки AS new заменяет устаревшую функцию VALUES().
  • INSERT IGNORE молча пропускает повторы и превращает ошибки в предупреждения, поэтому смотри SHOW WARNINGS.
  • REPLACE удаляет старую строку и вставляет новую с новым номером, и внешние ключи это чувствуют.

Попробуй сам

В песочнице база kino. Стартовый код заводит счётчик просмотров и трижды отмечает просмотр, в том числе пачкой.

Сравни rows affected у первых двух вставок. Замени псевдоним на VALUES(views) и прочитай SHOW WARNINGS. Попробуй REPLACE INTO halls VALUES (1, 'Большой', 150) и объясни ошибку.

Проверь себя

Задание

Дальше

Upsert делает два шага за одну команду. А если шагов пять: продать билет, списать деньги, отметить место? Их надо выполнить все или ни одного. Следующий урок про транзакции в InnoDB.

домашка

Домашка