MySQL: insert ... on duplicate key update - Делюсь опытом

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

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

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

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



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 = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

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 = INNODB

AUTO_INCREMENT = 1

CHARACTER SET utf8

COLLATE utf8_general_ci;

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

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



$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);

$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 уже давно умеет делать это за нас? Все требуемые нами действия можно выполнить одним запросом:



$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);

$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?

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



$query = "REPLACE INTO goods (id, itemName, price, cnt) VALUES ({$itemID}, '{$newItemName}', {$newItemPrice}, {$newItemCount});";
$res = mysql_query($query, $MyConnection);

$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) обнулится. Что будет весьма грустно. Ведь у нас правильный магазин и мы собираем статистику о популярности товаров.

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

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

Реклама: