"INSERT IGNORE" vs " INSERT ... przy aktualizacji duplikatu klucza"

Question

"INSERT IGNORE" vs " INSERT ... przy aktualizacji duplikatu klucza"

Podczas wykonywania instrukcji INSERT z wieloma wierszami, chcę pominąć zduplikowane wpisy, które w przeciwnym razie spowodowałyby błąd. Po pewnych badaniach moje opcje wydają się być użycie albo:

ON DUPLICATE KEY UPDATE co oznacza niepotrzebną aktualizację za jakimś kosztem, lub
INSERT IGNORE co oznacza zaproszenie na inne rodzaje niezapowiedzianego wślizgu.

Czy mam rację w tych założeniach? Jaki jest najlepszy sposób, aby po prostu pominąć wiersze, które mogą powodować duplikaty i po prostu przejdź do innych rzędów?

761

mysql insert

Author: P̲̳x͓L̳, 2009-02-14

Source

10 answers

Jeśli chcesz zobaczyć, co to wszystko oznacza, oto cios po uderzeniu wszystkiego:

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

Klucz podstawowy jest oparty na obu kolumnach tej tabeli szybkiego odniesienia. Klucz podstawowy wymaga unikalnych wartości.

Zacznijmy:

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

Uwaga, powyższe zaoszczędziło zbyt wiele dodatkowej pracy, ustawiając kolumnę równą sobie, żadna aktualizacja faktycznie nie jest potrzebna

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

A teraz kilka testów wielowarstwowych:

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

Żadne inne wiadomości nie zostały wygenerowane w konsoli, a teraz ma te 4 wartości w tabeli dane. Usunąłem wszystko oprócz (1,1), więc mogłem testować z tego samego pola gry

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

No i proszę. Ponieważ wszystko to zostało wykonane na świeżym stole bez prawie żadnych danych i nie w produkcji, czasy realizacji były mikroskopijne i nieistotne. Każdy z prawdziwymi danymi byłby mile widziany, aby go dodać.

159

Author: Paulus Maximus,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-10-21 18:23:12

Coś ważnego do dodania: gdy używasz INSERT IGNORE i masz łamanie kluczy, MySQL nie ostrzega!

Jeśli spróbujesz na przykład wstawić 100 rekordów na raz, z jednym wadliwym, uzyskasz tryb interaktywny:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

Jak widzisz: żadnych ostrzeżeń! To zachowanie jest nawet błędnie opisane w oficjalnej dokumentacji Mysql.

Jeśli twój skrypt musi zostać poinformowany, jeśli niektóre rekordy nie zostały dodane (z powodu łamania kluczy) musisz wywołać mysql_info () i przeanalizować ją dla wartości "duplikaty".

38

Author: Jens,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-06-03 23:45:33

Wiem, że to stare, ale dodam tę notkę w przypadku, gdy ktoś inny (jak ja) pojawi się na tej stronie, próbując znaleźć informacje na INSERT..Zignoruj.

Jak wspomniano powyżej, jeśli używasz INSERT..Ignoruj, błędy pojawiające się podczas wykonywania instrukcji INSERT są traktowane jako ostrzeżenia.

Jedna rzecz, która nie jest wyraźnie wymieniona, to wstawka..Ignorowanie spowoduje, że nieprawidłowe wartości zostaną dopasowane do najbliższych wartości po włożeniu (podczas gdy nieprawidłowe wartości spowodują zapytanie do przerwania, jeśli słowo kluczowe ignoruj nie zostało użyte).

17

Author: Chris,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-09-16 14:48:18

Rutynowo używam INSERT IGNORE, i brzmi to dokładnie tak, jak zachowanie, którego szukasz. Dopóki wiesz, że wiersze, które spowodowałyby konflikty indeksów, nie zostaną wstawione i odpowiednio zaplanujesz swój program, nie powinno to sprawiać żadnych problemów.

16

Author: David Z,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-02-14 05:53:59

Przy aktualizacji duplikatu klucza nie jest tak naprawdę w standardzie. Jest tak standardowo jak jest. Zobacz SQL MERGE .

Zasadniczo oba polecenia są alternatywnymi wersjami standardowych poleceń.

8

Author: Chris KL,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-02-14 05:57:00

Replace Into wydaje się być opcją. Możesz też sprawdzić za pomocą

IF NOT EXISTS(QUERY) Then INSERT

To wstawia lub usuwa, a następnie wstawia. Najpierw muszę sprawdzić.

6

Author: IEnumerator,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-11-19 08:47:57

Potencjalne niebezpieczeństwo Wstawienia ignoruj. Jeżeli próbujesz wstawić wartość VARCHAR dłużej, wtedy kolumna została zdefiniowana-wartość zostanie obcięta i wstawiona, nawet jeśli włączony jest tryb ścisły.

3

Author: LOL,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-19 23:33:58

Jeśli używasz insert ignore posiadanie instrukcji SHOW WARNINGS; na końcu zestawu zapytań wyświetli tabelę ze wszystkimi ostrzeżeniami, w tym które identyfikatory były duplikatami.

2

Author: Ray Foss,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-13 14:19:06

Jeśli chcesz wstawić do tabeli i konflikt klucza głównego lub unikalnego indeksu, zaktualizuje wiersz konfliktujący zamiast wstawiania tego wiersza.

Składnia:

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

Teraz tutaj, ta wstawka może wyglądać inaczej, co widziałeś wcześniej. Ta instrukcja insert próbuje wstawić wiersz w table1 z wartością a i b do kolumny column1 i column2 odpowiednio.

Zrozummy to stwierdzenie dogłębnie:

Na przykład: tutaj column1 jest zdefiniowany jako klucz podstawowy w table1.

Teraz, jeśli w table1 nie ma wiersza o wartości " a " w column1. Tak więc ta instrukcja wstawi wiersz w table1.

Teraz, jeśli w table1 znajduje się wiersz o wartości " a " w column2. Tak więc ta instrukcja zaktualizuje wartość column2 wiersza o wartość "c", gdzie wartość column1 to "a".

Więc jeśli chcesz wstawić nowy wiersz, w przeciwnym razie zaktualizuj ten wiersz w konflikcie klucza głównego lub unikalnego indeksu.
Czytaj więcej na ten link

2

Author: user2613580,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-13 15:00:50

score 923 · Accepted Answer

Polecam użycie INSERT...ON DUPLICATE KEY UPDATE.

Jeśli użyjesz INSERT IGNORE, wiersz nie zostanie wstawiony, jeśli spowoduje to zduplikowanie klucza. Ale oświadczenie nie wygeneruje błędu. Zamiast tego generuje ostrzeżenie. Przypadki te obejmują:

Wstawianie zduplikowanego klucza w kolumnach z ograniczeniami PRIMARY KEY lub UNIQUE.
Wstawianie NULL do kolumny z ograniczeniem NOT NULL.
Wstawianie wiersza do podzielonej tabeli, ale wartości, które wstawiasz, nie odwzorowują partycja.

Jeśli używasz REPLACE, MySQL faktycznie wykonuje DELETE, a następnie INSERT wewnętrznie, co ma pewne nieoczekiwane skutki uboczne:

przydzielany jest nowy identyfikator auto-increment.
wiersze zależne z kluczami obcymi mogą zostać usunięte (jeśli używasz kaskadowych kluczy obcych) lub zapobiec REPLACE.
wyzwalacze, które strzelają na {[8] } są wykonywane niepotrzebnie.
efekty uboczne są propagowane do niewolników replikacji też.

Korekta: zarówno REPLACE jak i {[2] } są niestandardowymi, zastrzeżonymi wynalazkami specyficznymi dla MySQL. ANSI SQL 2003 definiuje instrukcję MERGE, która może rozwiązać tę samą potrzebę( i więcej), ale MySQL nie obsługuje instrukcji MERGE.

Użytkownik próbował edytować ten post (edycja została odrzucona przez moderatorów). Edycja próbowała dodać twierdzenie, które INSERT...ON DUPLICATE KEY UPDATE powoduje przydzielenie nowego ID przyrostowego. To prawda, że nowy id jest wygenerowany , ale nie jest używany w zmienionym wierszu.

Zobacz prezentację poniżej, testowaną na serwerze Percona 5.5.28. Zmienna konfiguracyjna innodb_autoinc_lock_mode=1 (domyślna):

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

Powyższe pokazuje, że instrukcja IODKU wykrywa duplikat i wywołuje aktualizację, aby zmienić wartość u. Uwaga AUTO_INCREMENT=3 wskazuje, że identyfikator został wygenerowany, ale nie został użyty w wierszu.

Podczas gdy REPLACE usuwa oryginalny wiersz i wstawia nowy wiersz, generując i przechowując nowy auto-increment id:

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