MySQL Insert on duplicate key update

MySQL: insert … on duplicate key update

Часто ли вы используете данную конструкцию?

Наверняка любой программист, кто мало-мальски связан с вебом (а под «вебом» я понимаю LAMP — LinuxApacheMySQLPHP), сталкивался c ситуацией, когда перед вставкой новой записи в БД нужно проверить, а вдруг запись с таким ключом уже есть? И если таковая уже имеется, то надо не вставлять новую, а обновлять старую.

Пример применения простой. У вас есть интернет-магазин и вы периодически синхронизируете свою локальную БД с сайтом. Если товар уже присутствует, надо обновить остаток, а если в БД сайта товара нет, то надо добавить запись.

Есть простая таблица goods в БД сайта:


Из листинга понятно, что ключевое поле здесь id. Обратите внимание, что AUTO_INCREMENT для поля id не установлен. Айдишники скорее всего будет генерировать программа, с которой вы синхронизируете БД сайта. На сайте же достаточно того, что поле id будет уникальным. Мы, собственно, так и указали: PRIMARY KEY (id).

Самое очевидное, что приходит в голову — это в скрипте синхронизации (который у вас скорее всего на PHP) проверить наличие элемента с таким id. Получается довольно громоздкая конструкция. Вот фрагмент кода:

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

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

Конструкция insert … on duplicate key update работает именно таким образом, которым нам и нужно. MySQL попробует добавить запись, а если не получится — обновит. Т.е. вместо возвращения ошибки ERROR MySQL Duplicate entry будет выполнено обновление существующей записи.

Кто-нибудь обязательно подумает: а почему бы мне просто не использовать оператор REPLACE?

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

Но на этом сходство заканчивается. Более того, не будет выполнено наше главное условие: «обновить, если такая запись уже существует». Связано это с принципом работы оператора. Отличие в том, что insert … on duplicate key update пытается сначала добавить запись, а если не получается, то обновляет. REPLACE же сначала удалит существующую запись (если такая имеется) а потом вставит новую. В нашем примере, если запись уже существовала, мы потеряем данные. Временной штамп (поле ts) особой важности для нас, допустим, не представляет. А вот поле с количеством просмотров (views) обнулится. Что будет весьма грустно. Ведь у нас правильный магазин и мы собираем статистику о популярности товаров.

И, естественно, триггеры в этих случаях будут срабатывать разные. Впрочем, если вы активно используете триггеры, то этот материал вряд ли для вас).

Как-то так, если вкратце и понятным языком.

Реклама: