I’ve searched around but didn’t find if it’s possible.
I’ve this MySQL query:
Field id has a "unique index", so there can’t be two of them. Now if the same id is already present in the database, I’d like to update it. But do I really have to specify all these field again, like:
I’ve specified everything already in the insert.
A extra note, I’d like to use the work around to get the ID to!
I hope somebody can tell me what the most efficient way is.
8 Answers 8
The UPDATE statement is given so that older fields can be updated to new value. If your older values are the same as your new ones, why would you need to update it in any case?
For eg. if your columns a to g are already set as 2 to 8 ; there would be no need to re-update it.
Alternatively, you can use:
To get the id from LAST_INSERT_ID ; you need to specify the backend app you’re using for the same.
For LuaSQL, a conn:getlastautoid() fetches the value.
There is a MySQL specific extension to SQL that may be what you want — REPLACE INTO
However it does not work quite the same as ‘ON DUPLICATE UPDATE’
It deletes the old row that clashes with the new row and then inserts the new row. So long as you don’t have a primary key on the table that would be fine, but if you do, then if any other table references that primary key
You can’t reference the values in the old rows so you can’t do an equivalent of
I’d like to use the work around to get the ID to!
That should work — last_insert_id() should have the correct value so long as your primary key is auto-incrementing.
However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won’t be acceptable to you, as it deletes the old row that clashed via the unique key.
Someone else suggested before you can reduce some typing by doing:
Часто ли вы используете данную конструкцию?
Наверняка любой программист, кто мало-мальски связан с вебом (а под «вебом» я понимаю 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) обнулится. Что будет весьма грустно. Ведь у нас правильный магазин и мы собираем статистику о популярности товаров.
И, естественно, триггеры в этих случаях будут срабатывать разные. Впрочем, если вы активно используете триггеры, то этот материал вряд ли для вас).
Как-то так, если вкратце и понятным языком.
Несколько недель назад, я работал над проблемой клиента, который столкнулся с падением производительности БД и даже ее отказами, которые происходили приблизительно каждые 4 недели. Ничего особенного в окружении, в железе или запросах. В сущности, большей частью базы данных была одна таблица, в которой присутствовали, кроме прочего, INT AUTO_INCREMENT PRIMARY KEY и UNIQUE KEY .
Запросы, работающие с этой таблицей, почти все были типа INSERT . ON DUPLICATE KEY UPDATE (далее — INSERT ODKU ), где столбцы, перечисленные в INSERT , соответствовали столбцам с UNIQUE KEY . И выполнялись они с частотой, приблизительно 1500-2000 запросов в секунду, непрерывно 24 часа в сутки. Если вы хороши в математике, то наверное, уже догадались в чем дело.
Для целей дискуссии мы будем использовать следующую таблицу, как иллюстрацию к ситуации:
Теперь представим следующую последовательность событий:
Ничего необычного, да? Мы вставили один ряд в пустую таблицу и если мы сделаем SHOW CREATE TABLE , то мы увидим что счётчик AUTO_INCREMENT сейчас имеет значение 2 . Если мы сделаем INSERT ODKU в эту таблицу, то увидим следующее:
И теперь, даже если мы не вставили новый ряд, наш счётчик AUTO_INCREMENT вырос до 3 . Это, вообще-то, ожидаемое поведение. InnoDB проверяет ограничения в том порядке, в котором они были определены, и PRIMARY KEY всегда идёт первым. Поэтому MySQL проверяет наш INSERT , видит, что следующее значение AUTO_INCREMENT доступно и использует его, но потом, проверяет UNIQUE KEY и находит нарушение, поэтому вместо INSERT делает UPDATE . Если мы посмотрим счётчики handler status , мы можем увидеть, что был один запрос на вставку, который завершился неудачей, и один запрос на обновление, который прошёл успешно (это объясняет, почему изменены 2 ряда, а не 1).
В этом месте вы можете подумать — «Ну и что?». Давайте вернёмся к нашему клиенту. 1500 INSERT ODKU в секунду, непрерывно 24 часа в сутки. PRIMARY KEY их таблицы такой же, как я использовал в демонстрационной таблице — INT UNSIGNED . Считаем. Максимальное значение для INT UNSIGNED – это 4294967295 . Делим это на 1500 запросов в секунду и делим на 86400, что является количеством секунд в сутках, и мы получаем 33.1 дней, или чуть больше чем 4 недели. Совпадение? Я так не думаю. Итак, что именно происходит, когда мы выходим за пределы значения? Некоторое поведение может вас удивить. Вернёмся к нашей демонстрационной таблице и вставим в нее ряд с максимальным значением для столбца с AUTO_INCREMENT , а потом вставим ещё один.
Итак, мы попытались вставить ряд и это не вышло, т.к. AUTO_INCREMENT уже имел максимальное значение и запрос не прошёл. Но, что случится если мы попробуем сделать INSERT ODKU ? Сначала, посмотрим что у нас в таблице:
Выглядит нормально, да? 2 ряда изменено, очевидно, что для ряда который соответствовал условию username = "foo" , были обновлены host_id и last_modified , и мы можем радоваться. К сожалению, это не так:
Опа, обновлён был последний ряд, у которого id равен максимальному значению нашего AUTO_INCREMENT , а UNIQUE KEY на столбце username был проигнорирован.
Теперь мы можем легко понять в чем проблема клиента, чья база данных послужила вдохновением для этого поста. 1500 запросов в секунду, пытающихся заблокировать и обновить один и тот же ряд, ни к чему хорошему не приведут. Конечно, есть простое решение — изменить тип данных AUTO_INCREMENT -столбца c INT на BIGINT .
Оказывается, такое поведение документировано. Мануал говорит, что наш INSERT ODKU на таблице с несколькими уникальными индексами, будет эквивалентен запросу UPDATE update_test SET host_ LIMIT 1 и конечно оптимизатор скорее выберет PRIMARY , нежели вторичный UNIQUE .