Czy MySQL ignoruje wartości zerowe dla unikalnych ograniczeń?

289

Mam kolumnę e-mail, która chcę być wyjątkowa. Ale chcę też, aby akceptowała wartości zerowe. Czy moja baza danych może zawierać 2 puste wiadomości e-mail w ten sposób?

mysql database

— Hugo Mota
źródło

możliwy duplikat Unikalnego ograniczenia, które pozwala na puste wartości w MySQL

— Paulo Coghi - Przywróć Monikę

423

Tak, MySQL dopuszcza wiele wartości NULL w kolumnie z unikalnym ograniczeniem.

CREATE TABLE table1 (x INT NULL UNIQUE);
INSERT table1 VALUES (1);
INSERT table1 VALUES (1);   -- Duplicate entry '1' for key 'x'
INSERT table1 VALUES (NULL);
INSERT table1 VALUES (NULL);
SELECT * FROM table1;

Wynik:

x
NULL
NULL
1

Nie dotyczy to wszystkich baz danych. Na przykład program SQL Server 2005 i starsze zezwala tylko na jedną wartość NULL w kolumnie z unikalnym ograniczeniem.

— Mark Byers
źródło

37

doskonały komentarz na temat tego, jak to jest prawda w mysql, ale niekoniecznie ogólnie.

— user2910265,

11

Według SQLite FAQ zachowanie jest takie samo w MySQL, PostgreSQL, SQLite, Oracle i Firebird.

— Amir Ali Akbari

4

Zaktualizuj swoją odpowiedź. SQLServer 2008+ absolutnie na to pozwala, wystarczy dodać klauzulę WHERE ... w 2017 roku i tak nikt nie powinien być na starszej wersji niż 2008 ... stackoverflow.com/questions/767657/

— Mathieu Turcotte

ta mała funkcja była bardzo trudna do znalezienia odpowiedzi, która nie wymaga dodania nowej kolumny do bazy danych ani aktualizacji MySQL w bardzo starej aplikacji. Naprawdę szukałem rozwiązania takiego jak Postgres, w którym mogę korzystać z COALESCE, ale wydaje się, że odpowiedź brzmi: nie jest to błąd, jak jest zaprojektowany. Nawet WHERE column IS NOT NULLmnie nie zawodzi, ponieważ nie jest obsługiwany w mojej wersji MySQL. Czy ktoś wie, gdzie mógłbym spojrzeć?

— newdark-it,

1

Uwaga: działa to również w przypadku unikalnych indeksów, które mają więcej kolumn. Jeśli więc chcesz, aby kolumny a, b i c były unikalne, nadal możesz mieć w tabeli podwójne wiersze z null, b, c

— Mihai Crăiță

111

Z dokumentów :

„indeks UNIKALNY dopuszcza wiele wartości NULL dla kolumn, które mogą zawierać NULL”

Dotyczy to wszystkich silników oprócz BDB .

— Matthew Flaschen
źródło

3

BDB nie jest już dostępny w aktualnych wersjach MySQL (począwszy od 5.1.12).

— Alim Özdemir

1

Moje testy wydają się pokazywać, że baza danych Java Derby 10.13.1.1. podobnie dopuszcza tylko jeden null w kolumnie z unikalnym indeksem.

— chrisinmtown

7

Nie jestem pewien, czy autor pierwotnie pytał, czy pozwala to na duplikowanie wartości, czy też pojawiło się dorozumiane pytanie z pytaniem: „Jak zezwolić na duplikowanie NULLwartości podczas używania UNIQUE?”. Lub „Jak zezwolić tylko na jedną UNIQUE NULLwartość?”

Odpowiedź na pytanie została już udzielona, tak, możesz mieć zduplikowane NULLwartości podczas korzystania z UNIQUEindeksu.

Ponieważ natknąłem się na tę odpowiedź, szukając „jak zezwolić na jedną UNIQUE NULLwartość”. Dla każdego, kto może natknąć się na to pytanie, robiąc to samo, reszta mojej odpowiedzi jest dla ciebie ...

W MySQL nie możesz mieć jednej UNIQUE NULLwartości, jednak możesz mieć jedną UNIQUEpustą wartość, wstawiając wartość pustego łańcucha.

Ostrzeżenie: Liczby i typy inne niż łańcuch mogą mieć domyślną wartość 0 lub inną wartość domyślną.

— bluegman991
źródło

1

Ograniczenie nie ma nic wspólnego z indeksem. W rzeczywistości nie będzie nawet jednego wiersza o wartości NULL, mimo że nie ma innego takiego wiersza.

— Pijusn

1

@Pijusn Co rozumiesz przez „ograniczenie nie ma nic wspólnego z indeksem?” Również w drugim zdaniu nigdy nie powiedziałem, że możesz mieć wiersz o wartości NULL, dlatego powiedziałem na początku postu, że jest to rozwiązanie tylko wtedy, gdy nie jest ustawiony na używanie wartości zerowych.

— bluegman991

Chodziło mi o to, że dodanie nowego elementu kończy się niepowodzeniem nie z powodu UNIQUEograniczenia, ale z powodu NOT NULLograniczenia. Myślę, że ta odpowiedź nie ma znaczenia dla pytania, ponieważ pytanie dotyczy konkretnie zachowania UNIQUEograniczenia.

— Pijusn

@Pijusn Mam cię. Masz rację, usunąłem sformułowanie sugerujące inaczej. Źle przeczytałem pytanie. Uważam jednak, że odpowiedź nadal może być użyteczna dla użytkowników, którzy natkną się na to pytanie, tak jak to zrobiłem, próbując znaleźć sposób na uzyskanie unikalnej wartości „nic”, ale błędnie dopuszczają możliwość zerowania.

— bluegman991

1

Uznałem tę odpowiedź za przydatną. Jednak tutaj również znajduje się odpowiedź . Ten post był pierwszym wynikiem mojej wyszukiwarki Google, chociaż to odpowiedź i powiązane pytanie były tym, czego szukałem.

— kingledion

5

Unikaj dopuszczalnych unikalnych ograniczeń. Zawsze możesz umieścić kolumnę w nowej tabeli, ustawić ją na inną niż null i unikalną, a następnie wypełnić tę tabelę tylko wtedy, gdy masz dla niej wartość. Zapewnia to, że dowolna zależność klucza od kolumny może być poprawnie wymuszona i pozwala uniknąć problemów, które mogą być spowodowane przez wartości null.

— nvogel
źródło

6

Tak, ale to, co proponujesz, jest prawie dokładnie tym, co mysql robi już za kulisami. Po co wymyślać koło, jeśli ta funkcja jest wbudowana?

— ProfileTwist

2

Ponieważ to nie jest poprawny SQL. Wierzę, że ta wskazówka przyda się wszystkim, którzy chcą (lub potrzebują) agnostycznego projektu bazy danych.

— Arsen7

@ Arsen7 Co zrobić, jeśli masz wiele firm - każda z wieloma klientami. Wszystkie firmy z adresami e-mail ich klientów przechowujesz w jednym pliku. Nie możesz więc uczynić adresu email unikalnym, ponieważ różne firmy mogą mieć tego samego klienta. Musisz więc stworzyć unikalny złożony indeks id_biznesowego i adresu e-mail. Czy można to umieścić w nowej tabeli - jak wyjaśniono?

— Gerhard Liebenberg

4

Mam przypadek, w którym kolumna „e-mail” musi być unikalna LUB pusta. Musiałbym utworzyć nową tabelę z jedną kolumną „e-mail”, gdybym postępował zgodnie z twoją radą. Poleganie na tym specyficznym zachowaniu MySQL jest znacznie łatwiejsze, a wynik jest taki sam. Klient nie dba o to, czy przechowuję wiadomość e-mail w nowym stole, czy nie. Ponadto projektowanie agnostyczne baz danych jest często przereklamowane. W przypadku wielu projektów nie można i prawdopodobnie nie można tak łatwo zmienić jednego DB na inny.

— conradkleinespel

1

@ djmj na pewno, ale zależności funkcjonalne są ważne dla większości ludzi, a zerowa wersja unikatowego ograniczenia nie wymusza takich samych zależności jak wersja BCNF. Tak więc, która opcja jest mniej lub bardziej praktyczna, może zależeć od tego, które zależności są dla Ciebie ważne. Dlatego warto rozważyć utworzenie nowego stołu.

— nvogel