"INSERT IGNORE" против "INSERT ... ON DUPLICATE KEY UPDATE"

Question

"INSERT IGNORE" против "INSERT ... ON DUPLICATE KEY UPDATE"

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

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

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

953

mysql insert

Источник

user62701 14 фев '09 в 05:24

10 ответов

Решение

В случае, если вы хотите увидеть, что все это значит, вот пошагово все:

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Первичный ключ основан на обоих столбцах этой краткой справочной таблицы. Первичный ключ требует уникальных значений.

Давай начнем:

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

обратите внимание, что выше сэкономлено слишком много дополнительной работы, установив столбец, равный самому себе, обновление не требуется

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

а теперь несколько тестов с несколькими строками:

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

в консоли не было сгенерировано никаких других сообщений, и теперь у них есть эти 4 значения в данных таблицы. Я удалил все, кроме (1,1), чтобы я мог тестировать с того же игрового поля

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected

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

199

Источник

user1007660 21 окт '11 в 18:23

Что-то важное, что нужно добавить: при использовании INSERT IGNORE и наличии ключевых нарушений MySQL НЕ выдает предупреждение!

Если вы попытаетесь, например, вставить 100 записей за раз, причем одна неисправная, вы попадете в интерактивный режим:

Query OK, 99 rows affected (0.04 sec)
Records: 100 Duplicates: 1 Warnings: 0

Как видите: предупреждений нет! Это поведение даже неправильно описано в официальной документации Mysql.

Если ваш скрипт должен быть проинформирован, если некоторые записи не были добавлены (из-за нарушения ключа), вы должны вызвать mysql_info() и проанализировать его для значения "Duplicates".

47

Источник

user718730 21 апр '11 в 10:04

Я обычно использую INSERT IGNOREи это звучит точно так же, как вы ищете. Пока вы знаете, что строки, которые могут вызвать конфликты индексов, не будут вставлены, и вы планируете свою программу соответствующим образом, это не должно вызывать никаких проблем.

26

Источник

user56541 14 фев '09 в 05:53

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

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

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

23

Источник

user449647 16 сен '10 в 14:48

Replace В кажется, как вариант. Или вы можете проверить с

IF NOT EXISTS(QUERY) Then INSERT

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

10

Источник

user34819 14 фев '09 в 05:34

Потенциальная опасность INSERT IGNORE. Если вы пытаетесь вставить значение VARCHAR дольше, чем столбец был определен с помощью - значение будет усечено и вставлено ДАЖЕ, ЕСЛИ строгий режим включен.

9

Источник

user6232891 19 окт '17 в 23:33

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

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

8

Источник

user58110 14 фев '09 в 05:57

Добавляя к этому. Если вы используете оба INSERT IGNORE а также ON DUPLICATE KEY UPDATEв том же операторе обновление все равно произойдет, если вставка обнаружит повторяющийся ключ. Другими словами, обновление имеет приоритет над игнорированием. Однако еслиON DUPLICATE KEY UPDATE Само предложение вызывает ошибку дублирования ключа, эта ошибка будет проигнорирована.

Это может произойти, если у вас более одного уникального ключа или если ваше обновление пытается нарушить ограничение внешнего ключа.

CREATE TABLE test 
 (id BIGINT (20) UNSIGNED AUTO_INCREMENT, 
  str VARCHAR(20), 
  PRIMARY KEY(id), 
  UNIQUE(str));

INSERT INTO test (str) VALUES('A'),('B');

/* duplicate key error caused not by the insert, 
but by the update: */
INSERT INTO test (str) VALUES('B') 
 ON DUPLICATE KEY UPDATE str='A'; 

/* duplicate key error is suppressed */
INSERT IGNORE INTO test (str) VALUES('B') 
 ON DUPLICATE KEY UPDATE str='A';

8

Источник

user1701789 04 июл '20 в 01:46

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

4

Источник

user370238 13 мар '17 в 14:19

INSERT...ON DUPLICATE KEY UPDATE рекомендуется для предотвращения неожиданного управления исключениями.

Это решение работает, когда у вас есть 1 уникальное ограничение .

В моем случае я знаю, что col1 а также col2 сделать уникальный составной индекс.

Он отслеживает ошибку, но не генерирует исключение для дубликата. Что касается производительности, обновление на то же значение эффективно, поскольку MySQL это замечает и не обновляет его.

INSERT INTO table
  (col1, col2, col3, col4)
VALUES
  (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2)

Идея использовать этот подход возникла из комментариев на https://phpdelusions.net/pdo.

2

Источник

user5519544 30 ноя '19 в 02:26

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

Синтаксис:

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

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

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

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

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

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

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

2

Источник

user2613580 07 дек '17 в 21:00

Другие вопросы по тегам mysql insert

user20860 14 фев '09 в 05:51 2009-02-14 05:51 · Accepted Answer · 2009-02-14 05:51

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

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

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

Если вы используете REPLACEMySQL на самом деле делает 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 ... ON DUPLICATE KEY UPDATE"

10 ответов

Это решение работает, когда у вас есть **1 уникальное ограничение **.

Это решение работает, когда у вас есть 1 уникальное ограничение .