пример - mysql update or insert if not exists




«INSERT IGNORE» против «INSERT... ON DUPLICATE KEY UPDATE» (7)

ON DUPLICATE KEY UPDATE действительно не соответствует стандарту. Это стандартно, как REPLACE. См. SQL MERGE .

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

Выполняя INSERT со многими строками, я хочу пропустить повторяющиеся записи, которые в противном случае могли бы привести к сбою. После некоторых исследований мои варианты выглядят как использование:

  • ON DUPLICATE KEY UPDATE которое подразумевает ненужное обновление за небольшую плату или
  • INSERT IGNORE который подразумевает приглашение на другие виды сбоев в непредвиденных обстоятельствах.

Правильно ли я в этих предположениях? Каков наилучший способ просто пропустить строки, которые могут вызвать дубликаты, и просто перейти к другим строкам?


Если вы хотите вставить в таблицу и в конфликт первичного ключа или уникальный индекс, он обновит конфликтующую строку вместо того, чтобы вставлять эту строку.

Синтаксис:

insert into table1 set column1 = a, column2 = b on duplicate update column2 = c;

Теперь здесь этот оператор вставки может выглядеть по-другому, что вы видели ранее. Этот оператор insert пытается вставить строку в таблицу1 со значениями a и b в столбец столбца1 и столбец2 соответственно.

Давайте углубимся в это утверждение:

Например: здесь column1 определяется как первичный ключ в таблице1.

Теперь, если в таблице 1 нет строки, имеющей значение «a» в столбце 1. Таким образом, этот оператор вставляет строку в таблицу1.

Теперь, если в таблице 1 есть строка, имеющая значение «a» в столбце2. Таким образом, этот оператор обновит значение столбца строки с помощью «c», где значение столбца «a».

Поэтому, если вы хотите вставить новую строку, иначе обновите эту строку в конфликте первичного ключа или уникального индекса.
Подробнее об этой ссылке


Если использование insert ignore с помощью SHOW WARNINGS; в конце вашего набора запросов будет отображаться таблица со всеми предупреждениями, в том числе идентификаторы которых являются дубликатами.


Потенциальная опасность INSERT IGNORE. Если вы пытаетесь вставить значение VARCHAR дольше, тогда столбец был определен с: - значение будет усечено и вставлено. EVEN IF строгий режим включен.


Я бы рекомендовал использовать INSERT...ON DUPLICATE KEY UPDATE .

Если вы используете INSERT IGNORE , тогда строка на самом деле не будет вставлена, если она приведет к дублированию ключа. Но оператор не будет генерировать ошибку. Вместо этого он генерирует предупреждение. Эти случаи включают:

  • Вставка дублирующего ключа в столбцы с PRIMARY KEY или UNIQUE .
  • Вставка NULL в столбец с ограничением NOT NULL .
  • Вставка строки в секционированную таблицу, но значения, которые вы вставляете, не сопоставляются с разделом.

Если вы используете REPLACE , MySQL фактически выполняет DELETE за которым следует INSERT , что имеет некоторые неожиданные побочные эффекты:

  • Выделяется новый идентификатор автоинкремента.
  • Зависимые строки с внешними ключами могут быть удалены (если вы используете каскадные внешние ключи), либо не допускаете REPLACE .
  • Триггеры, DELETE , выполняются без необходимости.
  • Побочные эффекты распространяются также на подчиненные репликации.

исправление: оба REPLACE и INSERT...ON DUPLICATE KEY UPDATE - нестандартные, запатентованные изобретения, характерные для MySQL. ANSI SQL 2003 определяет оператор MERGE который может решить одну и ту же потребность (и многое другое), но MySQL не поддерживает оператор MERGE .

Пользователь попытался отредактировать этот пост (изменение было отклонено модераторами). Редактирование попыталось добавить утверждение о том, что INSERT...ON DUPLICATE KEY UPDATE вызывает выделение нового идентификатора автоматического инкремента. Это правда, что новый идентификатор сгенерирован , но он не используется в измененной строке.

См. Демонстрацию ниже, протестированную на Percona Server 5.5.28. Конфигурационная переменная innodb_autoinc_lock_mode=1 (по умолчанию):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Вышеприведенное показывает, что оператор IODKU обнаруживает дубликат и вызывает обновление для изменения значения u . Обратите внимание, что AUTO_INCREMENT=3 указывает, что идентификатор был сгенерирован, но не использовался в строке.

В то время как REPLACE удаляет исходную строку и вставляет новую строку, генерируя и сохраняя новый идентификатор автоматического инкремента:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

Я знаю, что это старо, но я добавлю это примечание на случай, если кто-то еще (как я) прибудет на эту страницу, пытаясь найти информацию о INSERT..IGNORE.

Как упоминалось выше, если вы используете INSERT..IGNORE, ошибки, возникающие при выполнении инструкции INSERT, рассматриваются как предупреждения.

Одна вещь, которая явно не упоминается, заключается в том, что INSERT..IGNORE приведет к тому, что недопустимые значения будут скорректированы до ближайших значений при вставке (тогда как недопустимые значения приведут к прерыванию запроса, если ключевое слово IGNORE не использовалось).


Replace Into выглядит как опция. Или вы можете проверить

IF NOT EXISTS(QUERY) Then INSERT

Это вставляет или удаляет, а затем вставляет. Я, как правило, предпочитаю проверять IF NOT EXISTS .







insert