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

Zacząłem od googlowania i znalazłem ten 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, aby rekord, który chcę wstawić, już nie istniał bez użycia pary zapytań (tj. Jedno zapytanie do sprawdzenia i jedno do wstawienia to zestaw wyników to pusty)?

Czy uniqueograniczenie pola gwarantuje insertniepowodzenie, jeśli już tam jest?

Wydaje się, że z ograniczonym ograniczeniem, gdy wydam wstawkę przez php, skrypt zaskrzecza.

posługiwać się INSERT IGNORE INTO table

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

istnieje również INSERT … ON DUPLICATE KEY UPDATEskładnia, wyjaśnienia znajdziesz na dev.mysql.com

Wpis z bogdan.org.ua zgodnie z webcache Google :

18 października 2007 r

Na początek: od najnowszej wersji MySQL składnia przedstawiona w tytule nie jest możliwa. Ale istnieje kilka bardzo łatwych sposobów na osiągnięcie tego, czego się oczekuje 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 stolik:

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ź sobie, że mamy automatyczny potok importujący transkrypty metadane z Ensembl i że z różnych powodów potok może być zepsuty na każdym etapie wykonywania. Dlatego musimy zapewnić dwie rzeczy:

1. wielokrotne wykonywanie potoku nie zniszczy naszej bazy danych

2. powtarzające się wykonania nie umrą z powodu błędów „duplikowania klucza podstawowego”.

Metoda 1: użycie WYMIANY

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 zastąpiony; jeśli jeszcze nie istnieje, zostanie utworzony. Jednak użycie tej metody nie jest skuteczne w naszym przypadku: nie musimy zastępować istniejących rekordów, wystarczy je pominąć.

Metoda 2: użycie 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 bazie danych, zostanie po cichu pominięty (zignorowany). (Mówiąc ściślej, cytat z podręcznika MySQL: „Jeśli używasz słowa kluczowego IGNORE, błędy występujące podczas wykonywania instrukcji INSERT są traktowane jako ostrzeżenia. Na przykład bez IGNORE wiersz, który powiela istniejący indeks UNIQUE lub wartość KLUCZ PODSTAWOWY w tabeli powoduje błąd duplikatu klucza, a instrukcja jest przerywana. ”.) Jeśli rekord jeszcze nie istnieje, zostanie utworzony.

Ta druga metoda ma kilka potencjalnych słabości, w tym brak przerywania zapytania w przypadku wystąpienia innego problemu (patrz instrukcja). Dlatego należy go używać, jeśli był wcześniej testowany bez słowa kluczowego IGNORE.

Metoda 3: użycie WSTAWIANIA… W DUPLIKACJI AKTUALIZACJI KLUCZOWEJ

Trzecią opcją jest użycie INSERT … ON DUPLICATE KEY UPDATE składni, aw części UPDATE po prostu nic nie rób jakiejś bezsensownej (pustej) operacji, takiej jak obliczanie 0 + 0 (Geoffray sugeruje wykonanie przypisania id = id dla silnika optymalizacji MySQL, aby zignorować tę operację). Zaletą tej metody jest to, że ignoruje tylko zduplikowane kluczowe zdarzenia i nadal przerywa inne błędy.

W ostateczności: ten post został zainspirowany przez Xaprb. Radzę też skonsultować jego drugi post na temat pisania elastycznych zapytań SQL.

Rozwiązanie:

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) 

Wyjaśnienie:

Najbardziej wewnętrzne zapytanie

SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

użyte jako WHERE NOT EXISTS-warunek wykrywa, czy istnieje już wiersz z danymi do wstawienia. Po znalezieniu jednego tego rodzaju wiersza zapytanie może zostać zatrzymane, dlatego też LIMIT 1(mikrooptymalizacja może zostać pominięta).

Zapytanie pośrednie

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

reprezentuje wartości do wstawienia. DUALodnosi się do specjalnego jednego wiersza, jednej tabeli kolumn domyślnie obecnych we wszystkich bazach danych Oracle (patrz https://en.wikipedia.org/wiki/DUAL_table ). Na serwerze MySQL w wersji 5.7.26 otrzymałem poprawne zapytanie, pomijając FROM DUAL, ale wydaje się, że starsze wersje (jak 5.5.60) wymagają tych FROMinformacji. Za pomocą WHERE NOT EXISTSzapytania pośredniego zwraca pusty zestaw wyników, jeśli najbardziej wewnętrzne zapytanie znalazło pasujące dane.

Zewnętrzne zapytanie

INSERT INTO `table` (`value1`, `value2`) 

wstawia dane, jeśli są zwracane przez zapytanie pośrednie.

po zduplikowaniu aktualizacji klucza lub wstawieniu ignorowania mogą być wykonalne rozwiązania z MySQL.

Przykład podwójnej aktualizacji aktualizacji klucza na podstawie 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 wstawienia zignoruj 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] ... ]

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

• klucz podstawowy, jeśli nie zastępczy
• unikalne ograniczenie dla kolumny
• unikatowe ograniczenie wielokolumnowe

Niestety, wydaje się to zwodniczo proste. Wiem, że wygląda źle w porównaniu do linku, który nam udostępniasz. ;-(

Ale nigdy nie daję tej odpowiedzi, ponieważ wydaje się, że zaspokaja twoją potrzebę. (Jeśli nie, może to spowodować aktualizację twoich wymagań, co byłoby również „dobrą rzeczą”).

Edytowane : Jeśli wstawka złamie unikalne ograniczenie bazy danych, na poziomie bazy danych zostanie zgłoszony wyjątek, przekazywany przez sterownik. Z pewnością zatrzyma twój skrypt z niepowodzeniem. W PHP musi być możliwe rozwiązanie tej sprawy ...

Oto funkcja PHP, która wstawi wiersz tylko wtedy, gdy wszystkie określone wartości kolumn nie istnieją jeszcze w tabeli.

• Jeśli jedna z kolumn się różni, 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'
  )
);
?>

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

Jeśli rekord istnieje, zostanie zastąpiony; jeśli jeszcze nie istnieje, zostanie utworzony.

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

Istnieje kilka odpowiedzi, które obejmują sposób rozwiązania tego problemu, jeśli masz UNIQUEindeks, który możesz sprawdzić za pomocą ON DUPLICATE KEYlub INSERT IGNORE. Nie zawsze tak jest, a ponieważ UNIQUEma ograniczenie długości (1000 bajtów), możesz nie być w stanie tego zmienić. Na przykład musiałem pracować z metadanymi w WordPress (wp_postmeta ).

W końcu rozwiązałem go za pomocą dwóch zapytań:

UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);

Zapytanie 1 jest zwykłym UPDATEzapytaniem, które nie działa, gdy nie ma danego zbioru danych. Zapytanie 2 jest INSERTzależne od a NOT EXISTS, tzn. INSERTJest wykonywane tylko wtedy, gdy zestaw danych nie istnieje.

Warto zauważyć, że INSERT IGNORE będzie nadal zwiększał klucz podstawowy, niezależnie od tego, czy instrukcja zakończyła się sukcesem, czy nie, tak jak normalny INSERT.

Spowoduje to luki w kluczach podstawowych, które mogą spowodować, że programista będzie niestabilny psychicznie. Lub jeśli twoja aplikacja jest źle zaprojektowana i zależy od idealnych przyrostowych kluczy podstawowych, może to sprawić ból głowy.

Zajrzyj do innodb_autoinc_lock_mode = 0(ustawienie serwera i ma niewielki spadek wydajności) lub użyj najpierw SELECT, aby upewnić się, że twoje zapytanie nie zakończy się niepowodzeniem (co również obejmuje zwiększenie wydajności i dodatkowy kod).

Zaktualizuj lub wstaw bez znanego klucza podstawowego

Jeśli masz już unikalny lub podstawowy klucz, inne odpowiedzi z jednym z nich INSERT INTO ... ON DUPLICATE KEY UPDATE ...lub REPLACE INTO ...powinny działać poprawnie (pamiętaj, że zamień na usuwa, jeśli istnieje, a następnie wstawia - w ten sposób nie aktualizuje częściowo istniejących wartości).

Ale jeśli masz wartości dla some_column_idi some_type, których kombinacja jest znana jako unikalna. I chcesz zaktualizować, some_valuejeśli istnieje, lub wstaw, jeśli nie istnieje. I chcesz to zrobić za pomocą tylko jednego zapytania (aby uniknąć transakcji). To może być rozwiązanie:

INSERT INTO my_table (id, some_column_id, some_type, some_value)
SELECT t.id, t.some_column_id, t.some_type, t.some_value
FROM (
    SELECT id, some_column_id, some_type, some_value
    FROM my_table
    WHERE some_column_id = ? AND some_type = ?
    UNION ALL
    SELECT s.id, s.some_column_id, s.some_type, s.some_value
    FROM (SELECT NULL AS id, ? AS some_column_id, ? AS some_type, ? AS some_value) AS s
) AS t
LIMIT 1
ON DUPLICATE KEY UPDATE
some_value = ?

Zasadniczo zapytanie jest wykonywane w ten sposób (mniej skomplikowane niż mogłoby się wydawać):

• Wybierz istniejący wiersz za pomocą WHEREdopasowania klauzuli.
• Zjednoczenie, które daje wynik z potencjalnie nowym wierszem (tabelą s), w którym wartości kolumn są jawnie podane (s.id ma wartość NULL, więc wygeneruje nowy identyfikator automatycznego przyrostu).
• Jeżeli istniejąca rząd zostanie znaleziony, potencjalnego nowego rzędu z tabeli sodrzuca się (ze względu na ograniczenie nr 1 w tabeli t), a zawsze będzie wyzwalać ON DUPLICATE KEYktóre będąUPDATEsome_value kolumnę.
• Jeśli istniejący wiersz nie zostanie znaleziony, wówczas potencjalny nowy wiersz zostanie wstawiony (zgodnie z tabelą s).

Uwaga: Każda tabela w relacyjnej bazie danych powinna mieć co najmniej podstawową idkolumnę automatycznego przyrostu . Jeśli go nie masz, dodaj go, nawet jeśli nie potrzebujesz go od pierwszego wejrzenia. Jest to zdecydowanie potrzebne do tej „sztuczki”.