Wstaw warunkowy MySQL


98

Mam trudności z utworzeniem warunkowej WSTAWY

Mam x_table z kolumnami (instancja, użytkownik, pozycja), w których identyfikator instancji jest unikalny. Chcę wstawić nowy wiersz tylko wtedy, gdy użytkownik nie ma już podanej pozycji.

Na przykład próba wstawienia instance = 919191 user = 123 item = 456

Insert into x_table (instance, user, item) values (919191, 123, 456) 
    ONLY IF there are no rows where user=123 and item=456 

Każda pomoc lub wskazówka we właściwym kierunku byłaby bardzo mile widziana.

Odpowiedzi:


115

Jeśli twój DBMS nie nakłada ograniczeń na to, z której tabeli wybierasz, kiedy wykonujesz wstawianie, spróbuj:

INSERT INTO x_table(instance, user, item) 
    SELECT 919191, 123, 456
        FROM dual
        WHERE NOT EXISTS (SELECT * FROM x_table
                             WHERE user = 123 
                               AND item = 456)

W tym przypadku dualjest to tabela z tylko jednym wierszem (pierwotnie w Oracle, teraz także w mysql). Logika jest taka, że ​​instrukcja SELECT generuje pojedynczy wiersz danych z wymaganymi wartościami, ale tylko wtedy, gdy wartości nie zostały jeszcze znalezione.

Alternatywnie, spójrz na oświadczenie MERGE.


1
Czy dualw tym przypadku musi być utworzona wcześniej, czy jest to jakaś specjalna „tymczasowa” tabela utworzona przez zapytanie w celu użycia samego zapytania?
itsmequinn

8
Jeśli jeszcze nie masz dual, musisz go utworzyć. CREATE TABLE dual ( dummy CHAR(1) DEFAULT 'x' NOT NULL CHECK (dummy = 'x') PRIMARY KEY ); INSERT INTO dual VALUES('x'); REVOKE ALL ON dual FROM PUBLIC; GRANT SELECT ON dual TO PUBLIC;
Jonathan Leffler

Dzięki, że warto wiedzieć
itsmequinn

11
Zauważ, że w MySQL nie potrzebujesz tak naprawdę tabeli o nazwie „dual”, aby istnieć: jest to specjalna nazwa tabeli, której można użyć do wybrania czegokolwiek z niej.
Christopher Schultz,

3
MySQL „szanuje” koncepcję „podwójnego”, ale tak naprawdę nie istnieje. Na przykład, jeśli SELECT COUNT(*) FROM dualzawsze otrzymujesz 1i SELECT 'foo' FROM dualzawsze otrzymujesz foo. Ale nie możesz SELECT * FROM duali nie możesz, DESCRIBE dualani nic takiego. Nie sprawdzałem, ale nie sądzę też, że możesz cofnąć uprawnienia dual. Więc warto zwrócić uwagę, że działa zgodnie z oczekiwaniami ... z wyjątkiem, gdy nie robi;)
Christopher Schultz

52

Możesz również użyć tego, INSERT IGNOREktóry po cichu ignoruje wstawianie zamiast aktualizowania lub wstawiania wiersza, gdy masz unikalny indeks na (użytkownik, element).

Zapytanie będzie wyglądać następująco:

INSERT IGNORE INTO x_table(instance, user, item) VALUES (919191, 123, 456)

Możesz dodać unikalny indeks za pomocą CREATE UNIQUE INDEX user_item ON x_table (user, item).


2
Według dokumentacji MySQL INSERT IGNORE jest odpowiednikiem REPLACE ... INSERT IGNORE: zachowaj stare wiersze. REPLACE: zachowaj nowe wiersze. Oba działają na unikalnych kluczach.
Paul Kenjora,

Najlepsza odpowiedź wszechczasów
jmojico

30

Czy kiedykolwiek próbowałeś czegoś takiego?

INSERT INTO x_table
SELECT 919191 as instance, 123 as user, 456 as item
FROM x_table
WHERE (user=123 and item=456)
HAVING COUNT(*) = 0;

o tak ! sprytne i przyjemne rozwiązanie z tylko jedną instrukcją SELECT
java acm

Miły. Działa to dla postgresql przy pierwszej próbie, gdzie zaakceptowana odpowiedź nie wydawała mi się.
mightypile

10

Z UNIQUE(user, item), wykonaj następujące czynności:

Insert into x_table (instance, user, item) values (919191, 123, 456) 
  ON DUPLICATE KEY UPDATE user=123

user=123bit jest „no-op”, aby dopasować składnię ON DUPLICATEklauzuli bez faktycznie robi nic, gdy istnieją duplikaty.


1
Nie mogę ustawić unikatu (użytkownika, przedmiotu), ponieważ mogą wystąpić sytuacje, w których wiele wystąpień ma ten sam użytkownik i przedmiot ... po prostu nie, gdy robię tę wstawkę.
The Unknown

3

Jeśli nie chcesz ustawiać unikalnego ograniczenia, działa to jak urok:

INSERT INTO `table` (`column1`, `column2`) SELECT 'value1', 'value2' FROM `table` WHERE `column1` = 'value1' AND `column2` = 'value2' HAVING COUNT(`column1`) = 0

Mam nadzieję, że to pomoże !


To było idealne, dziękuję!
AndyGaskell

2

To czego chcesz INSERT INTO table (...) SELECT ... WHERE .... z podręcznika MySQL 5.6 .

W twoim przypadku jest to:

INSERT INTO x_table (instance, user, item) SELECT 919191, 123, 456 
WHERE (SELECT COUNT(*) FROM x_table WHERE user=123 AND item=456) = 0

A może, ponieważ nie używasz skomplikowanej logiki do określenia, czy uruchomić, INSERTczy nie, możesz po prostu ustawić UNIQUEklucz na kombinacji tych dwóch kolumn, a następnie użyć INSERT IGNORE.


Sprawdziłeś swoją odpowiedź? ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where...Wciąż dostaję (działa 5.6.34)
hlin117

Myślę, że musisz powiedzieć from dualwcześniej where.
hlin117

@ hlin117 W MariaDB FROM DUALjest ustawieniem domyślnym, jeśli nie było odwołań do żadnych tabel ( zobacz tę dokumentację ).
Yeti

1

Jeśli dodasz ograniczenie, które (x_table.user, x_table.item) jest unikatowe, wstawienie kolejnego wiersza z tym samym użytkownikiem i elementem zakończy się niepowodzeniem.

na przykład:

mysql> create table x_table ( instance integer primary key auto_increment, user integer, item integer, unique (user, item));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x_table (user, item) values (1,2),(3,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into x_table (user, item) values (1,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into x_table (user, item) values (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 2

To nie tak. Może istnieć wiele wierszy z tym samym użytkownikiem, ale nie może istnieć wiele wierszy z parą użytkownik-element.
Matthew Flaschen

Tak tak. Źle to przeczytałem. Edytowano, aby utworzyć wiązanie na parze.
NickZoic

Fajny komentarz ... uruchamiam zapytanie w PHP używając mysql_query ("INSERT") i zostawiam część lub umieść, dlatego nie ostrzega mnie o błędzie, więc nie muszę sprawdzać
Angel.King.47

1

Chociaż dobrze jest sprawdzić, czy przed wstawieniem danych nie ma duplikatów, sugeruję, aby umieścić unikalne ograniczenie / indeks na swoich kolumnach, aby przez pomyłkę nie można było wstawić zduplikowanych danych.


1

Aby rozwiązać problem, możesz skorzystać z następującego rozwiązania:

INSERT INTO x_table(instance, user, item) 
    SELECT 919191, 123, 456
        FROM dual
        WHERE 123 NOT IN (SELECT user FROM x_table)

Chociaż ten kod może odpowiedzieć na pytanie, dostarczenie dodatkowego kontekstu dotyczącego tego, jak i / lub dlaczego rozwiązuje problem, poprawiłoby długoterminową wartość odpowiedzi.
Nic3500,

0

Nieznaczna modyfikacja odpowiedzi Alexa, możesz też po prostu odwołać się do istniejącej wartości kolumny:

Insert into x_table (instance, user, item) values (919191, 123, 456) 
  ON DUPLICATE KEY UPDATE user=user

0

Więc ten oznacza PostgreSQL

INSERT INTO x_table
SELECT NewRow.*
FROM (SELECT 919191 as instance, 123 as user, 456 as item) AS NewRow
LEFT JOIN x_table
ON x_table.user = NewRow.user AND x_table.item = NewRow.item
WHERE x_table.instance IS NULL

-1
Insert into x_table (instance, user, item) values (919191, 123, 456) 
    where ((select count(*) from x_table where user=123 and item=456) = 0);

Składnia może się różnić w zależności od Twojej bazy danych ...


2
Do czego to służy? MySQL?
Umair A.
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.