„INSERT IGNORE” vs „INSERT… ON DUPLICATE KEY UPDATE”

Podczas wykonywania INSERTinstrukcji z wieloma wierszami chcę pominąć zduplikowane wpisy, które w przeciwnym razie spowodowałyby błąd. Po niektórych badaniach wydaje się, że moimi opcjami są:

• ON DUPLICATE KEY UPDATE co oznacza niepotrzebną aktualizację za pewną opłatą, lub
• INSERT IGNORE co oznacza zaproszenie na inne rodzaje niezapowiedzianych niespodzianek.

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 przejść do innych wierszy?

Poleciłbym użyć INSERT...ON DUPLICATE KEY UPDATE.

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

• Wstawianie duplikatu klucza w kolumnach z PRIMARY KEYlub z UNIQUEograniczeniami.
• Wstawianie wartości NULL do kolumny z NOT NULLograniczeniem.
• Wstawianie wiersza do tabeli podzielonej na partycje, ale wstawiane wartości nie są mapowane na partycję.

Jeśli używasz REPLACEMySQL faktycznie robi DELETEpo którym następuje INSERTwewnętrznie, który ma pewne nieoczekiwane skutki uboczne:

• Przydzielono nowy identyfikator automatycznego przyrostu.
• Zależne wiersze z kluczami obcymi mogą zostać usunięte (jeśli używasz kaskadowych kluczy obcych) lub też uniemożliwić REPLACE.
• Wyzwalacze, które DELETEsię uruchamiają, są wykonywane niepotrzebnie.
• Efekty uboczne są również propagowane do replik.

korekta: zarówno REPLACEi INSERT...ON DUPLICATE KEY UPDATEsą niestandardowe, opatentowane wynalazki specyficzne dla MySQL. ANSI SQL 2003 definiuje MERGEinstrukcję, która może zaspokoić tę samą potrzebę (i więcej), ale MySQL nie obsługuje tej MERGEinstrukcji.

Użytkownik próbował edytować ten post (zmiana została odrzucona przez moderatorów). Edycja próbowała dodać oświadczenie, które INSERT...ON DUPLICATE KEY UPDATEpowoduje przydzielenie nowego identyfikatora automatycznego przyrostu. To prawda, że ​​nowy identyfikator jest generowany , ale nie jest używany w zmienionym wierszu.

Zobacz demonstrację poniżej, przetestowaną na Percona Server 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ę w celu zmiany wartości u. Uwaga AUTO_INCREMENT=3wskazuje, że identyfikator został wygenerowany, ale nie został użyty w wierszu.

Podczas REPLACEgdy usuwa oryginalny wiersz i wstawia nowy wiersz, generując i przechowując nowy identyfikator automatycznego przyrostu:

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

Jeśli chcesz zobaczyć, co to wszystko znaczy, oto cios wszystkiego po wszystkim:

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 opiera się na obu kolumnach tej tabeli szybkich odniesień. Klucz podstawowy wymaga unikalnych wartości.

Zaczynajmy:

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ę na taką samą, nie wymagając żadnej aktualizacji

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

a teraz niektóre testy wielu wierszy:

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 komunikaty nie zostały wygenerowane w konsoli, a teraz ma te 4 wartości w danych tabeli. Usunąłem wszystko oprócz (1,1), aby móc 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

Więc masz to. Ponieważ wszystko to zostało wykonane na świeżym stole bez prawie żadnych danych i nie w produkcji, czasy wykonania były mikroskopijne i nieistotne. Każdy, kto ma rzeczywiste dane, chętnie je włączy.

Coś ważnego do dodania: Gdy używasz INSERT IGNORE i masz poważne naruszenia zasad, MySQL NIE wyświetla ostrzeżenia!

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

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

Jak widać: brak ostrzeżeń! To zachowanie jest nawet błędnie opisane w oficjalnej dokumentacji MySQL.

Jeśli twój skrypt wymaga powiadomienia, jeśli niektóre rekordy nie zostały dodane (z powodu naruszenia klucza), musisz wywołać mysql_info () i przeanalizować go pod kątem wartości „Duplikaty”.

Rutynowo używam INSERT IGNOREi brzmi to dokładnie takie zachowanie, jakiego szukasz. Tak długo, jak wiesz, że wiersze, które spowodowałyby konflikty indeksów, nie zostaną wstawione i odpowiednio planujesz swój program, nie powinno to powodować żadnych problemów.

Wiem, że to stare, ale dodam tę notatkę na wypadek, gdyby ktoś (jak ja) przybył na tę stronę, próbując znaleźć informacje na temat INSERT..IGNORE.

Jak wspomniano powyżej, jeśli użyjesz INSERT..IGNORE, błędy występujące podczas wykonywania instrukcji INSERT są traktowane jako ostrzeżenia.

Jedną rzeczą, która nie jest wyraźnie wymieniona, jest to, że INSERT..IGNORE spowoduje, że niepoprawne wartości zostaną dostosowane do najbliższych wartości po wstawieniu (podczas gdy niepoprawne wartości spowodują przerwanie zapytania, jeśli nie zostanie użyte słowo kluczowe IGNORE).

NA DUPLIKACIE KLUCZOWA AKTUALIZACJA nie jest tak naprawdę standardem. Jest to tak standardowe, jak REPLACE. Zobacz scalanie SQL .

Zasadniczo oba polecenia są wersjami standardowych poleceń o alternatywnej składni.

ReplaceOpcja wydaje się być opcją. Lub możesz to sprawdzić za pomocą

IF NOT EXISTS(QUERY) Then INSERT

Spowoduje to wstawienie lub usunięcie, a następnie wstawienie. I mają tendencję, aby przejść do IF NOT EXISTSsprawdzenia w pierwszej kolejności.

Potencjalne niebezpieczeństwo INSERT IGNORE. Jeśli próbujesz wstawić wartość VARCHAR dłużej, wówczas kolumna została zdefiniowana za pomocą - wartość zostanie obcięta i wstawiona NAWET JEŚLI włączony jest tryb ścisły.

W przypadku korzystania insert ignoreposiadające SHOW WARNINGS;oświadczenie na końcu zestawu zapytań pokaże tabelę ze wszystkimi ostrzeżeniami, w tym identyfikatory, które były duplikatami.

Jeśli chcesz wstawić do tabeli i w przypadku konfliktu klucza podstawowego lub indeksu unikalnego, zaktualizuje on wiersz powodujący konflikt zamiast wstawiania tego wiersza.

Składnia:

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

Teraz tutaj instrukcja wstawiania może wyglądać inaczej niż wcześniej. Ta instrukcja wstawiania próbuje wstawić wiersz w tabeli 1 o wartości aib odpowiednio w kolumnie kolumna 1 i kolumna 2.

Dogłębnie zrozummy to stwierdzenie:

Na przykład: tutaj kolumna 1 jest zdefiniowana jako klucz podstawowy w tabeli 1.

Teraz, jeśli w tabeli 1 nie ma wiersza o wartości „a” w kolumnie 1. Tak więc ta instrukcja wstawi wiersz do tabeli1.

Teraz, jeśli w tabeli 1 znajduje się wiersz o wartości „a” w kolumnie 2. Zatem ta instrukcja zaktualizuje wartość kolumny 2 wiersza o „c”, gdzie wartość kolumny 1 to „a”.

Więc jeśli chcesz wstawić nowy wiersz, w przeciwnym razie zaktualizuj ten wiersz o konflikcie klucza podstawowego lub unikalnego indeksu.
Przeczytaj więcej na ten link

INSERT...ON DUPLICATE KEY UPDATE jest preferowany, aby zapobiec nieoczekiwanemu zarządzaniu wyjątkami.

To rozwiązanie działa tylko wtedy, gdy masz ** 1 unikalne ograniczenie **

W moim przypadku wiem o tym col1i col2tworzę unikalny indeks złożony.

Śledzi błąd, ale nie zgłasza wyjątku na duplikacie. Jeśli chodzi o wydajność, aktualizacja o tej samej wartości jest skuteczna, ponieważ MySQL to zauważa i nie aktualizuje

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

Pomysł zastosowania tego podejścia zrodził się z komentarzy na phpdelusions.net/pdo .