Pytanie Jak "wstawić, jeśli nie istnieje" w MySQL?


Zacząłem od szukania go i znalazłem to artykuł który mówi o tabelach mutex.

Mam stolik z ~ 14 milionami rekordów. Jeśli chcę dodać więcej danych w tym samym formacie, czy istnieje sposób zapewnienia, że ​​rekord, który chcę wstawić, jeszcze nie istnieje bez użycia pary zapytań (np. Jedno zapytanie do sprawdzenia i jedno do wstawienia to zestaw wyników to pusty)?

Czy a unique ograniczenie na polu gwarantuje insert zawiedzie, jeśli już tam jest?

Wydaje się, że z jedynie ograniczenie, kiedy wydaję wkładkę przez php, skrypty przechwytują.


672
2017-09-01 08:56


pochodzenie


dev.mysql.com/doc/refman/5.0/en/if.html - Uğur Gümüşhan
Widzieć stackoverflow.com/questions/44550788/... do dyskusji o niepalaniu wartości auto_inc. - Rick James
@RickJames - to ciekawa q .. ale nie jestem pewien czy jest to bezpośrednio związane z q :) - warren
Zostało to wspomniane w komentarzu, a to inne pytanie stwierdziło, że to pytanie było "dokładnym duplikatem". Czułem więc, że dobrym pomysłem jest połączenie pytań na korzyść innych. - Rick James
Och, nigdy nie myślę patrzeć na pasek boczny. - Rick James


Odpowiedzi:


posługiwać się INSERT IGNORE INTO table

widzieć http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

jest także INSERT … ON DUPLICATE KEY UPDATE składnia, możesz znaleźć wyjaśnienia dev.mysql.com


Opublikuj na bogdan.org.ua według Google Webcache:

18 października 2007

Aby rozpocząć: od najnowszego MySQL, składnia przedstawiona w tytule nie jest   możliwy. Ale jest kilka bardzo prostych sposobów na osiągnięcie tego, co jest   oczekiwano przy użyciu istniejącej funkcjonalności.

Istnieją 3 możliwe rozwiązania: użycie INSERT IGNORE, REPLACE lub   INSERT ... ON DUPLICATE KEY UPDATE.

Wyobraź sobie, że mamy stół:

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Teraz wyobraźmy sobie, że mamy automatyczny import transkryptów   meta-danych z Ensembl, i że z różnych przyczyn rurociąg   może zostać złamane na każdym etapie realizacji. Dlatego musimy zapewnić dwa   rzeczy: 1) powtarzające się egzekucje z rurociągu nie zniszczą naszego   baza danych i 2) powtórzone egzekucje nie umrą z powodu "duplikowania"   błędy klucza głównego.

Metoda 1: użycie REPLACE

To jest bardzo proste:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Jeśli rekord istnieje, zostanie nadpisany; jeśli jeszcze nie   istnieje, zostanie stworzony. Jednak korzystanie z tej metody nie jest wydajne   w naszym przypadku: nie musimy nadpisywać istniejących rekordów, jest w porządku   po prostu je omijać.

Metoda 2: za pomocą INSERT IGNORE Również bardzo proste:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Tutaj, jeśli 'ensembl_transcript_id' jest już obecny w   bazy danych, zostanie on pominięty (zignorowany). (Być bardziej dokładnym,   oto cytat z podręcznika referencyjnego MySQL: "Jeśli używasz IGNORE   słowo kluczowe, błędy występujące podczas wykonywania instrukcji INSERT   traktowane jako ostrzeżenia. Na przykład, bez IGNORE, wiersz, który   duplikuje istniejący indeks UNIQUE lub PRIMARY KEY w tabeli   powoduje błąd duplikatu klucza, a instrukcja zostaje przerwana. ".) Jeśli   rekord jeszcze nie istnieje, zostanie utworzony.

Ta druga metoda ma kilka potencjalnych słabych punktów, w tym   nie powoduje przerwania kwerendy w przypadku wystąpienia jakiegokolwiek innego problemu (patrz:   podręcznik). Dlatego powinno się go używać, jeśli wcześniej testowano bez   IGNORE słowo kluczowe.

Jest jeszcze jedna opcja: użyć INSERT ... ON DUPLICATE KEY UPDATE   składni, aw części UPDATE po prostu nic nie rób bez znaczenia   (pusta) operacja, taka jak obliczanie 0 + 0 (Geoffray sugeruje wykonanie   id = id assignment dla silnika optymalizacji MySQL, aby to zignorować   operacja). Zaletą tej metody jest to, że ignoruje ona jedynie duplikaty   kluczowe zdarzenia i nadal przerywa wykonywanie innych błędów.

W ostatecznym ogłoszeniu: ten post został zainspirowany przez Xaprb. Radziłbym też   skonsultuj swój drugi wpis na temat pisania elastycznych zapytań SQL.


681
2017-09-01 09:02



i czy mogę połączyć to z "opóźnionym", aby przyspieszyć skrypt? - warren
tak, wstawić opóźniony może przyspieszyć rzeczy dla ciebie. Wypróbuj to - knittl
Tak, i pamiętaj o tym REPLACE INTO powoduje USUŃ następnie WSTAW, nie AKTUALIZUJ - bobobobo
INSERT … ON DUPLICATE KEY UPDATE jest lepszy, ponieważ nie usuwa wiersza, zachowując go auto_increment kolumny i inne dane. - redolent
Tylko po to, aby poinformować wszystkich. Za pomocą INSERT … ON DUPLICATE KEY UPDATE metoda zwiększa dowolną kolumnę AUTO_INCREMENT z nieudaną wstawką. Prawdopodobnie dlatego, że nie jest tak naprawdę nie powiodło się, ale UPDATE'd. - not2qubit


INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1 

Alternatywnie, zewnętrzna SELECT oświadczenie może odnosić się do DUAL w celu obsługi przypadku, w którym tabela jest początkowo pusta:

INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1 

154
2018-06-11 18:38



czy możesz podać więcej informacji o tym, jak z tego korzystać? - Alex V
Co powiesz na wykonanie tego zapytania? - Toni Michel Caubet
Ten wariant jest odpowiedni, jeśli nie istnieje unikalny klucz w tabeli (INSERT IGNORE i INSERT ON DUPLICATE KEY wymagają unikalnych ograniczeń klawiszy) - rabudde
Jeśli używasz "z podwójnego" na linii 2 zamiast "z tabeli", to nie potrzebujesz klauzuli "limit 1". - Rich
Co jeśli stuff for value1 i stuff for value2 są identyczne? To rzuciłoby a Duplicate column name - Robin


po duplikowaniu aktualizacji kluczy, lub wstaw ignoruj może być realistycznymi rozwiązaniami z MySQL.


Przykład po duplikowaniu aktualizacji kluczy aktualizacja oparta na mysql.com

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Przykład wstaw ignoruj na podstawie mysql.com

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Lub:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Lub:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

46
2017-09-01 09:05





Wszelkie proste ograniczenia powinny wykonać zadanie, jeśli wyjątek jest akceptowalny. Przykłady:

  • klucz podstawowy, jeśli nie jest zastępczy
  • unikalne ograniczenie na kolumnie
  • unikalne ograniczenie dla wielu kolumn

Przepraszam, wydaje się to pozornie proste. Wiem, że źle wygląda połączenie z nami. ;-(

Ale nigdy nie tracę odpowiedzi, ponieważ wydaje się, że wypełniasz twoją potrzebę. (Jeśli nie, może to spowodować aktualizację twoich wymagań, co byłoby również "dobrą rzeczą" (TM)).

Edytowane: Jeśli wstawienie spowodowałoby złamanie ograniczenia unikalności bazy danych, wyjątek jest odrzucany na poziomie bazy danych, przekazywany przez sterownik. Z pewnością zatrzyma twój skrypt, z niepowodzeniem. W PHP musi być możliwe adresowanie tego przypadku ...


23
2017-09-01 09:01



Dodałem wyjaśnienie do pytania - czy twoja odpowiedź nadal obowiązuje? - warren
Uważam, że tak. Unikalne ograniczenie spowoduje niepowodzenie nieprawidłowych wstawień. Uwaga: musisz uporać się z tym niepowodzeniem w swoim kodzie, ale jest to dość standardowe. - KLE
na razie mam zamiar trzymać się rozwiązania, które zaakceptowałem - ale przyjrzymy się obsłudze błędów INSERT itp., ponieważ aplikacja rośnie - warren
INSERT IGNORE zasadniczo zmienia wszystkie błędy w ostrzeżenia, aby twój skrypt nie został przerwany. Następnie można wyświetlić wszelkie ostrzeżenia za pomocą polecenia SHOW WARNINGS. I kolejny ważna uwaga: Ograniczenia UNIQUE nie działają z wartościami NULL, tj. row1 (1, NULL) i row2 (1, NULL) zostaną wstawione (chyba że złamane zostanie inne ograniczenie, na przykład klucz podstawowy). Niefortunny. - Simon East


REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Jeśli rekord istnieje, zostanie nadpisany; jeśli jeszcze nie istnieje, zostanie utworzony.


17
2017-07-06 14:35



REPLACE może usunąć wiersz, a następnie wstawić zamiast aktualizacji. Efektem ubocznym jest to, że ograniczenia mogą usuwać inne obiekty i usuwane są wyzwalacze. - xmedeko


Oto funkcja PHP, która wstawi wiersz tylko wtedy, gdy wszystkie podane wartości kolumn nie istnieją już w tabeli.

  • Jeśli jedna z kolumn różni się, wiersz zostanie dodany.

  • Jeśli tabela jest pusta, wiersz zostanie dodany.

  • Jeśli istnieje wiersz, w którym wszystkie określone kolumny mają określone wartości, wiersz nie zostanie dodany.

    function insert_unique($table, $vars)
    {
      if (count($vars)) {
        $table = mysql_real_escape_string($table);
        $vars = array_map('mysql_real_escape_string', $vars);
    
        $req = "INSERT INTO `$table` (`". join('`, `', array_keys($vars)) ."`) ";
        $req .= "SELECT '". join("', '", $vars) ."' FROM DUAL ";
        $req .= "WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
    
        foreach ($vars AS $col => $val)
          $req .= "`$col`='$val' AND ";
    
        $req = substr($req, 0, -5) . ") LIMIT 1";
    
        $res = mysql_query($req) OR die();
        return mysql_insert_id();
      }
    
      return False;
    }
    

Przykładowe użycie:

<?php
insert_unique('mytable', array(
  'mycolumn1' => 'myvalue1',
  'mycolumn2' => 'myvalue2',
  'mycolumn3' => 'myvalue3'
  )
);
?>

17
2018-03-06 19:56



Dość kosztowne, jeśli masz ogromny ładunek wstawek. - Эџad Дьdulяңмaи
prawda, ale skuteczna, jeśli chcesz dodać konkretne badania kontrolne - Charles Forest


Spróbuj wykonać następujące czynności:

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
  INSERT INTO beta (name) VALUES ('John')
  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END

16
2018-05-10 09:58





Replace może ci pomóc.


11
2017-09-01 09:02



dzięki za pomysł - wolałbym nie wymieniać 14 milionów rekordów (lub do ilu ich rośnie) przy dodawaniu nowych źródeł danych :) - warren
Tak, code.openark.org/blog/mysql/replace-into-think-twice - bobobobo
Mam odwołanie za pomocą klucza obcego dla tabela i część usuwania delete spowodował błąd ograniczenia klucza obcego. Wydaje się, że to zły i nieoczekiwany efekt uboczny. - raider33