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

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) 

Explanation:

Najskrytsze zapytanie

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

Używany jako WHERE NOT EXISTS-warunek wykrywa, czy istnieje już wiersz z danymi do wstawienia. Po znalezieniu jednego wiersza tego rodzaju, zapytanie może zostać zatrzymane, stąd LIMIT 1 (Mikro-optymalizacja, może zostać pominięta).

Zapytanie pośrednie

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

Przedstawia wartości, które mają być wstawione. DUAL odnosi się do specjalnego jednego wiersza, jednej kolumny tabeli obecny przez domyślne we wszystkich bazach danych Oracle (zobacz https://en.wikipedia.org/wiki/DUAL_table ). na serwerze MySQL w wersji 5.7.26 otrzymałem poprawne zapytanie przy pominięciu FROM DUAL, ale starsze wersje (jak 5.5.60) wydają się wymagać informacji FROM. Za pomocą WHERE NOT EXISTS zapytanie pośrednie zwraca pusty zestaw wyników, jeśli najbardziej wewnętrzne zapytanie znalazło pasujące dane.

Zapytanie zewnętrzne

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

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

Przy duplicate key update , lub insert ignore mogą być realne rozwiązania z MySQL.

Przykład na duplikat aktualizacji klucza Aktualizacja 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 insert ignore 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] ... ]

Każde proste ograniczenie powinno wykonać zadanie, jeśli wyjątek jest akceptowalny. Przykłady:

• Klucz podstawowy, jeśli nie zastępczy
• unikalne ograniczenie na kolumnie
• multi-column unique constraint

Przepraszam, to wydaje się zwodniczo proste. Wiem, że źle to wygląda z linkiem, którym się z nami dzielisz. ;-(

Ale nigdy nie udzielam tej odpowiedzi, ponieważ wydaje się ona zaspokajać Twoje potrzeby. (Jeśli nie, może to spowodować aktualizację wymagań, które byłyby " a Good Thing " (TM) also).

Edited: jeśli insert złamie unikalne ograniczenie bazy danych, wyjątkiem jest throw na poziomie bazy danych, przekazywany przez sterownik. To z pewnością zatrzyma twój scenariusz, z porażką. W PHP musi być możliwe adresowanie tego przypadku ...

Tutaj znajduje się funkcja PHP, która wstawia wiersz tylko wtedy, gdy wszystkie podane wartości kolumn nie istnieją już w tabeli.

• Jeśli jedna z kolumn jest inna, wiersz zostanie dodany.

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

• Jeśli istnieje wiersz, w którym wszystkie podane 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 nadpisany; jeśli jeszcze nie istnieje, zostanie utworzony.

Spróbuj:

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ą, jak rozwiązać ten problem, jeśli masz UNIQUE indeks, który możesz sprawdzić za pomocą ON DUPLICATE KEY lub INSERT IGNORE. Nie zawsze tak jest, a ponieważ UNIQUE ma 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 to dwoma zapytaniami:

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 zapytaniem UPDATE bez efektu, gdy dany zbiór danych nie istnieje. Query 2 is an INSERT która zależy od NOT EXISTS, tzn. {[7] } jest wykonywana tylko wtedy, gdy zbiór danych nie istnieje.

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

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

Spójrz na innodb_autoinc_lock_mode = 0 (ustawienie serwera i ma niewielki wpływ na wydajność), lub użyj najpierw SELECT, aby upewnij się, że Twoje zapytanie nie zawiedzie(które również zawiera trafienie wydajności i dodatkowy kod).

Update or insert without known primary key

Jeśli masz już klucz unique lub primary, Pozostałe odpowiedzi z INSERT INTO ... ON DUPLICATE KEY UPDATE ... lub REPLACE INTO ... powinny działać poprawnie(zauważ, że zastąp 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_id i some_type, których kombinacja jest znana jako unikalna. I chcesz zaktualizować some_value, jeśli istnieje, lub wstawić, jeśli nie istnieje. I chcesz to zrobić w jednym zapytaniu (aby uniknąć wykorzystania 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 wykonuje się w ten sposób (mniej skomplikowane, niż może się wydawać):

• wybierz istniejący wiersz poprzez dopasowanie klauzuli WHERE.
• Unia, która daje wynik z potencjalnym nowym wierszem (Tabela s), gdzie wartości kolumn są jawnie podane (s.id jest NULL, więc wygeneruje nowy identyfikator auto-increment).
• Jeśli znaleziono istniejący wiersz, to potencjalny nowy wiersz z tabeli s jest odrzucony (ze względu na ograniczenie 1 w tabeli t), A zawsze wywoła ON DUPLICATE KEY, która będzie UPDATE kolumną some_value.
• Jeśli istniejący wiersz nie został znaleziony, to potencjalny nowy wiersz jest wstawiany(jak podano w tabeli s).

uwaga: każda tabela w relacyjnej bazie danych powinna mieć co najmniej podstawową kolumnę auto-increment id. Jeśli tego nie masz, dodaj go, nawet jeśli nie potrzebujesz go na pierwszy rzut oka. Jest to zdecydowanie potrzebne do tej "sztuczki".