MySQL: insert … on duplicate key update

MySQL: insert … on duplicate key update

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

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

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

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

MySQL CREATE TABLE goods ( id int(11) NOT NULL COMMENT ‘Ключ’, itemName varchar(50) DEFAULT NULL COMMENT ‘Наименование’, ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Временной штамп’, price decimal(19, 2) DEFAULT NULL COMMENT ‘Цена’, cnt int(11) DEFAULT NULL COMMENT ‘Остаток на складе’, views int(11) NOT NULL default 0 COMMENT ‘Просмотров’ PRIMARY KEY (id))ENGINE = INNODBAUTO_INCREMENT = 1CHARACTER SET utf8COLLATE utf8_general_ci;123456789101112131415  CREATE TABLE goods (  id int(11) NOT NULL COMMENT ‘Ключ’,  itemName varchar(50) DEFAULT NULL COMMENT ‘Наименование’,  ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Временной штамп’,  price decimal(19, 2) DEFAULT NULL COMMENT ‘Цена’,  cnt int(11) DEFAULT NULL COMMENT ‘Остаток на складе’,  views int(11) NOT NULL default 0 COMMENT ‘Просмотров’  PRIMARY KEY (id))ENGINE = INNODBAUTO_INCREMENT = 1CHARACTER SET utf8COLLATE utf8_general_ci;
(adsbygoogle = window.adsbygoogle || []).push({});

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

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

PHP$query = «SELECT 1 from goods WHERE id={$itemID}»;$res = mysql_query($query, $MyConnection);if(mysql_num_rows($res)==0){$query = «INSERT INTO goods (id, itemName, price, cnt) VALUES ({$itemID}, ‘{$newItemName}’, {$newItemPrice}, {$newItemCount});»;}else{$query = «UPDATE goods SET itemName='{$newItemName}’, price={$newItemPrice}, cnt={$newItemCount} WHERE id={$itemID}»;}$res = mysql_query($query, $MyConnection);1234567891011  $query = «SELECT 1 from goods WHERE id={$itemID}»;$res = mysql_query($query, $MyConnection);if(mysql_num_rows($res)==0){$query = «INSERT INTO goods (id, itemName, price, cnt) VALUES ({$itemID}, ‘{$newItemName}’, {$newItemPrice}, {$newItemCount});»;}else{$query = «UPDATE goods SET itemName='{$newItemName}’, price={$newItemPrice}, cnt={$newItemCount} WHERE id={$itemID}»;}$res = mysql_query($query, $MyConnection);

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

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

PHP$res = mysql_query(«INSERT INTO goods (id, itemName, price, cnt) VALUES ({$itemID}, ‘{$newItemName}’, {$newItemPrice}, {$newItemCount}) ON DUPLICATE KEY UPDATE itemName='{$newItemName}’, price={$newItemName}, cnt=$newItemCount};», $MyConnection);123  $res = mysql_query(«INSERT INTO goods (id, itemName, price, cnt) VALUES ({$itemID}, ‘{$newItemName}’, {$newItemPrice}, {$newItemCount}) ON DUPLICATE KEY UPDATE itemName='{$newItemName}’, price={$newItemName}, cnt=$newItemCount};», $MyConnection);

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

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

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

PHP$query = «REPLACE INTO goods (id, itemName, price, cnt) VALUES ({$itemID}, ‘{$newItemName}’, {$newItemPrice}, {$newItemCount});»;$res = mysql_query($query, $MyConnection);1234  $query = «REPLACE INTO goods (id, itemName, price, cnt) VALUES ({$itemID}, ‘{$newItemName}’, {$newItemPrice}, {$newItemCount});»;$res = mysql_query($query, $MyConnection);

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

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

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