Часто ли вы используете данную конструкцию?
Наверняка любой программист, кто мало-мальски связан с вебом (а под «вебом» я понимаю LAMP — LinuxApacheMySQLPHP), сталкивался c ситуацией, когда перед вставкой новой записи в БД нужно проверить, а вдруг запись с таким ключом уже есть? И если таковая уже имеется, то надо не вставлять новую, а обновлять старую.
Пример применения простой. У вас есть интернет-магазин и вы периодически синхронизируете свою локальную БД с сайтом. Если товар уже присутствует, надо обновить остаток, а если в БД сайта товара нет, то надо добавить запись.
Есть простая таблица goods в БД сайта:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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. Получается довольно громоздкая конструкция. Вот фрагмент кода:
1 2 3 4 5 6 7 8 9 10 11 |
$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 уже давно умеет делать это за нас? Все требуемые нами действия можно выполнить одним запросом:
1 2 3 |
$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?
Действительно, в этом случае мы тоже обойдемся всего одним запросом к БД:
1 2 3 4 |
$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) обнулится. Что будет весьма грустно. Ведь у нас правильный магазин и мы собираем статистику о популярности товаров.
И, естественно, триггеры в этих случаях будут срабатывать разные. Впрочем, если вы активно используете триггеры, то этот материал вряд ли для вас).
Как-то так, если вкратце и понятным языком.
Реклама: