У меня есть столбец электронной почты, который я хочу сделать уникальным. Но я также хочу, чтобы он принимал нулевые значения. Может ли моя база данных таким образом иметь 2 пустых письма?
MySQL игнорирует нулевые значения в уникальных ограничениях?
comment
возможный дубликат Уникальное ограничение, разрешающее пустые значения в MySQL
- person Paulo Coghi schedule 24.08.2015
Ответы (5)
Да, MySQL допускает несколько значений NULL в столбце с уникальным ограничением.
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;
Результат:
x
NULL
NULL
1
Это верно не для всех баз данных. Например, SQL Server 2005 и более ранние версии допускают только одно значение NULL в столбце с уникальным ограничением.
person
Mark Byers
schedule
14.09.2010
отличный комментарий о том, насколько это верно в mysql, но не обязательно в целом.
- person user2910265; 13.09.2014
Согласно FAQ по SQLite, поведение является То же самое в MySQL, PostgreSQL, SQLite, Oracle и Firebird.
- person Amir Ali Akbari; 07.11.2016
Пожалуйста, обновите свой ответ. SQLServer 2008+ абсолютно позволяет это, вам просто нужно добавить предложение WHERE ... в 2017 году никто не должен использовать более старую версию, чем 2008 ... stackoverflow.com/questions/ 767657 /
- person Mathieu Turcotte; 19.07.2017
было очень трудно найти ответ на эту небольшую функцию, которая не требует добавления нового столбца в базу данных или обновления MySQL в очень старом приложении. Я действительно искал такое решение, как Postgres, где я могу использовать COALESCE, но, похоже, ответ всегда заключается в том, что это не ошибка, а то, как оно спроектировано. Даже
WHERE column IS NOT NULL, похоже, не подводит меня, поскольку он не поддерживается в моей версии MySQL. Кто-нибудь знает, где я могу посмотреть?
- person newdark-it; 20.11.2018
примечание: это также работает также с уникальными индексами с большим количеством столбцов. Итак, если вы хотите, чтобы столбцы a, b и c были уникальными, вы все равно можете иметь в таблице двойные строки с null, b, c
- person Mihai Crăiță; 13.10.2019
Из документов:
"индекс UNIQUE допускает несколько значений NULL для столбцов, которые могут содержать NULL"
Это относится ко всем движкам, кроме BDB.
person
Matthew Flaschen
schedule
14.09.2010
BDB больше не доступен в текущих версиях mysql (начиная с 5.1.12).
- person Alim Özdemir; 01.06.2015
Мое тестирование, похоже, показывает, что база данных Java Derby v10.13.1.1. аналогично допускает только один null в столбце с уникальным индексом.
- person chrisinmtown; 30.03.2018
Я не уверен, что автор изначально просто спрашивал, допускает ли это дублирование значений, или здесь подразумевается вопрос: «Как разрешить дублирование значений NULL при использовании UNIQUE?» Или "Как разрешить только одно UNIQUE NULL значение?"
На этот вопрос уже был дан ответ, да, при использовании индекса UNIQUE у вас могут быть повторяющиеся значения NULL.
Поскольку я наткнулся на этот ответ, когда искал «как разрешить одно значение UNIQUE NULL». Для всех, кто может наткнуться на этот вопрос, делая то же самое, остальная часть моего ответа предназначена для вас ...
В MySQL у вас не может быть одного UNIQUE NULL значения, однако вы можете иметь одно UNIQUE пустое значение, вставив его со значением пустой строки.
Предупреждение: числовые и другие типы, кроме строки, могут по умолчанию равняться 0 или другому значению по умолчанию.
person
bluegman991
schedule
23.11.2016
Ограничение не имеет ничего общего с индексом. Фактически, вы даже не сможете иметь одну строку со значением NULL, несмотря на то, что другой такой строки нет.
- person Pijusn; 15.01.2017
@Pijusn Что вы имеете в виду под ограничением, не имеющим ничего общего с индексом? Также по поводу вашего второго предложения я никогда не говорил, что у вас может быть строка со значением NULL, поэтому я заявил в начале сообщения, что это решение, только если он не настроен на использование значений NULL.
- person bluegman991; 15.01.2017
Я имел в виду, что добавление нового элемента не удается не из-за ограничения
UNIQUE, а из-за ограничения NOT NULL. Я думаю, что этот ответ не имеет отношения к вопросу, потому что вопрос конкретно касается поведения ограничения UNIQUE.
- person Pijusn; 15.01.2017
@Pijusn Я тебя понял. Вы правы, я убрал формулировку, говорящую об обратном. Я неправильно прочитал вопрос. Но я считаю, что ответ по-прежнему может быть полезен для пользователей, которые наткнулись на этот вопрос, как это сделал я, пытаясь найти способ иметь уникальное значение «ничего», но ошибочно допускающие нулевую способность.
- person bluegman991; 16.01.2017
Я нашел этот ответ полезным; Мне нужно однозначно идентифицировать пользователей по 3 параметрам, но в зависимости от значения второго параметра третий параметр является необязательным. Вместо этого мне нужно будет выполнить проверку Rails.
- person Devon Parsons; 16.03.2017
Я нашел этот ответ полезным. Однако ответ на него также можно найти здесь. Этот пост был первым результатом моего поиска в Google, хотя этот ответ и связанный вопрос были тем, что я искал.
- person kingledion; 08.09.2017
Также вы можете создать сгенерированный столбец с
IFNULL(field_name, '') и создать для него индекс.
- person toraman; 21.05.2021
Избегайте ограничений уникальности, допускающих значение NULL. Вы всегда можете поместить столбец в новую таблицу, сделать его непустым и уникальным, а затем заполнить эту таблицу только тогда, когда у вас есть для нее значение. Это гарантирует, что любая зависимость ключа от столбца может быть правильно применена, и позволяет избежать любых проблем, которые могут быть вызваны значениями NULL.
person
nvogel
schedule
14.09.2010
Да, но то, что вы предлагаете, - это почти то же самое, что mysql уже делает за кулисами. Зачем изобретать колесо, если эта функция встроена?
- person ProfileTwist; 12.03.2014
Потому что это недопустимый SQL. Я считаю, что этот совет будет полезен всем, кто хочет (или нуждается) в независимом от базы данных дизайне.
- person Arsen7; 09.12.2014
@ Arsen7 Что делать, если у вас несколько предприятий - у каждого несколько клиентов. Вы храните весь бизнес с адресами электронной почты их клиентов в одном файле. Таким образом, вы не можете сделать email_address уникальным, потому что у разных компаний может быть один и тот же клиент. Итак, вам нужно создать составной уникальный индекс business_id и email_address. Можно ли поместить это в новую таблицу - как объяснено?
- person Gerhard Liebenberg; 02.02.2015
@GerhardLiebenberg, да, конечно, это возможно.
- person nvogel; 02.02.2015
У меня есть случай, когда столбец электронной почты должен быть уникальным ИЛИ нулевым. Если бы я последовал вашему совету, мне пришлось бы создать новую таблицу с одним столбцом электронной почты. Полагаться на это специфическое поведение Mysql намного проще, и результат тот же. Клиента не волнует, сохраню ли я электронное письмо в новой таблице или нет. Кроме того, дизайн, не зависящий от базы данных, часто переоценивается. Для многих проектов вы не можете и, вероятно, не сможете просто так легко переключаться с одной БД на другую.
- person conradkleinespel; 18.06.2019
@conradkleinespel Я тоже не вижу смысла в дополнительной таблице. На сервере MS SQL вы можете определить собственные уникальные ограничения с условием
WHERE column IS NOT NULL. В некоторых таблицах у вас даже есть несколько ограничений, допускающих значение NULL. Наличие таблицы для каждого ограничения выглядит очень накладно.
- person djmj; 31.05.2020
@djml, Ключи (то есть неприводимые наборы уникальных столбцов, не допускающих значения NULL) - это просто то, что подразумевается BCNF, а именно каждый определитель становится суперключом. Предположение об универсальном отношении предполагает, что любой ключ можно скрыть, просто объединив две таблицы вместе и потенциально сделав некоторые атрибуты обнуляемыми. Вся идея нормализации состоит в том, чтобы избежать такой ситуации, потому что ключи и зависимости между ними очень важны.
- person nvogel; 01.06.2020
Поскольку BCNF подразумевает, что это не является практическим решением. Запрашивающий уже разрешает null в своем приложении, и с этим решением я дал решение. MySQL предоставляет встроенное проверенное решение. В качестве альтернативы вам не нужно использовать ограничение уникальности базы данных, как я уже упоминал, вы можете применить ограничение проверки или проверить его в приложении, чтобы гарантировать уникальность.
- person djmj; 01.06.2020
@djmj конечно, но функциональные зависимости важны для большинства людей, а версия с уникальным ограничением, допускающая значение NULL, не обеспечивает тех же зависимостей, что и версия BCNF. Итак, какой вариант более или менее практичен, может зависеть от того, какие зависимости важны для вас. Вот почему стоит подумать о создании новой таблицы.
- person nvogel; 02.06.2020
Простой ответ: No, it doesn't
Объяснение: Согласно определению уникальных ограничений (SQL-92)
Ограничение уникальности выполняется тогда и только тогда, когда никакие две строки в таблице не имеют одинаковых ненулевых значений в уникальных столбцах.
Это утверждение может иметь две интерпретации:
- Никакие две строки не могут иметь одинаковые значения, т.е.
NULLиNULLнедопустимы. - Никакие две ненулевые строки не могут иметь значений, т.е.
NULLиNULLв порядке, ноStackOverflowиStackOverflowне допускаются.
Поскольку MySQL следует второй интерпретации, в столбце ограничения UNIQUE допускается несколько значений NULL. Во-вторых, если вы попытаетесь понять концепцию NULL в SQL, вы обнаружите, что два NULL значения вообще нельзя сравнивать, поскольку NULL в SQL относится к недоступному или неназначенному значению (вы не можете ничего сравнивать ни с чем). Теперь, если вы не разрешаете использовать несколько значений NULL в столбце ограничений UNIQUE, вы сокращаете значение NULL в SQL. Я бы резюмировал свой ответ, сказав:
MySQL поддерживает ограничение UNIQUE, но не за счет игнорирования значений NULL
person
Akshay Katiha
schedule
07.11.2020
