MySQL, REPLACE INTO и ON DUPLICATE KEY UPDATE
23 декабря 2012
В MySQL есть два расширения, которые позволяют атомарно вставить или обновить запись. Годятся они для штук с довольно высокой нагрузкой, когда без блокировок не работает типичный сценарий:
- Выбрать запись.
- Если выбралась — обновить.
- Если пусто — вставить.
REPLACE
REPLACE INTO vote SET user_id = 13, object_id = 42, value = 7
INSERT INTO ON DUPLICATE KEY UPDATE
INSERT INTO vote SET user_id = 13, object_id = 42, value = 7 ON DUPLICATE KEY UPDATE value = 7
Сходства
И то и то выражение в итоге даст примерно тот же результат. При нарушении ограничения на уникальность (то есть UNIQUE KEY или PRIMARY KEY) не будет никакой ошибки. Для ключа user_id, object_id
в базе будет единственная запись с значением 7
.
Отличия
Кроме очевидного отличия в синтаксисе есть и очень важно отличие в том, как всё это работает. REPLACE
при срабатывании ограничения удаляет запись (и вызывает ON DELETE CASCADE
) и затем вставляет новую, что не так быстро, как ON DUPLICATE KEY UPDATE
, который обновляет запись без удаления.
Комментарии RSS по email OK
Там еще с автоинкремент тонкость вроде была.
Немного тавтология, но такой вариант немного более универсальный:
Вже писав про такі інсерти - sergunik.name/?p=393
здесь не только в скорости разница. если есть какие-то поля (не указанные в запросе) со значениями отличными от DEFAULT, то UPDATE их не затронет. А REPLACE вставит новую запись с уже DEFAULT-значениями.
не подскажите из каких соображений отсутствуют соответствующие метод для моделей в YII?
довольно часто они были необходимы. в итоге использовал данное решение www.askdev.ru/php/4199/Yii-Расширить-CActiveRecord/
Отсутствует потому как это специфичная для MySQL штука, а в Yii всё более-менее универсальное: работает для MySQL, SQLite, PostgreSQL, MSSQL и Oracle.
Если не ошибаюсь – INSERT INTO ON DUPLICATE KEY UPDATE увеличивает AUTO_INCREMENT.
vasa_c метко подметил - ON DUPLICATE KEY UPDATE в большинстве случаев - более верное решение.
"Если не ошибаюсь – INSERT INTO ON DUPLICATE KEY UPDATE увеличивает AUTO_INCREMENT." Как раз наоборот т.к.
"REPLACE при срабатывании ограничения удаляет запись (и вызывает ON DELETE CASCADE) и затем вставляет новую"
"Если не ошибаюсь – INSERT INTO ON DUPLICATE KEY UPDATE увеличивает AUTO_INCREMENT." - только для InnoDB таблиц
привет всем. а какой метод самый быстрый?
"Там еще с автоинкремент тонкость вроде была."
Пришлось столкнуться, и вот оно как:
INSERT INTO ON DUPLICATE KEY UPDATE: На INNODB увеличивает AUTO_INCREMENT. На MyISAM AUTO_INCREMENT не меняется. Lacmus прав.
REPLACE: И на INNODB и на MyISAM AUTO_INCREMENT меняется. Более того, измененная запись меняет значение автоинкрементного поля. Например, если id у записи был 2, то после замены легко может стать 10. Все из-за удаления существовавшей записи и создания такой же новой, с другим id.