Используя InnoDB / MySQLi, у меня есть простая таблица: mytable . Таблица имеет четыре поля: id (primary, auto_inc), field1 , field2 , field3 . Все они BIGINT и, за исключением id , могут быть NULL .
Я добавил уникальное ограничение:
ALTER TABLE mytable ADD UNIQUE INDEX(field1,field2,field3);
Тем не менее, я вполне могу добавить следующие строки без генерации ошибки. Я хотел бы, чтобы это создавало ошибку «duplicate», но это не так:
INSERT INTO mytable VALUES (NULL,3,NULL) INSERT INTO mytable VALUES (NULL,3,NULL)
Он генерирует только «повторяющуюся» ошибку, если все поля имеют значения, отличные от NULL, например,
INSERT INTO mytable VALUES (2,3,4) INSERT INTO mytable VALUES (2,3,4)
Как я могу сказать MySQL генерировать ошибки «duplicate», даже если одно (или несколько) полей имеет значения NULL ?
EDIT: это ранее было добавлено как «ошибка» для MySQL: http://bugs.mysql.com/bug.php?id=25544
Вы не можете сравнивать NULL (если вы сравниваете что-либо с NULL, даже NULL = NULL, результаты всегда FALSE ) это поведение задокументировано в MySQL ref.
Индекс UNIQUE создает ограничение, так что все значения в индексе должны быть разными. Произошла ошибка, если вы попытаетесь добавить новую строку с ключевым значением, которое соответствует существующей строке. Для всех двигателей индекс UNIQUE допускает множественные значения NULL для столбцов, которые могут содержать NULL.
Поэтому я думаю, что единственный способ – определить столбцы NOT NULL или обработать эту проблему в триггере.
Корень проблемы – по сравнению с NULL-s. Вы должны понимать логическое значение NULL. И это не «ценность». Не «нулевое значение» или «неизвестное значение» , но «нет значения» . Это большая разница.
Вот почему создание уникального индекса NULL-возможно – плохая идея. Вы не можете сравнивать NULL, так как вы не можете сравнить два значения, оба из которых отсутствуют. Таким образом, СУБД не может поддерживать NULL-s, чтобы быть уникальным, поскольку сравнение не применимо к ним обычным способом. Да, такие вещи, как <=> существуют в MySQL (или IS NULL в других СУБД), но это касается технического разрешения того, как справляться с сравнениями со значениями NULL, но не логическими.
Итак, вы находитесь в середине XY-проблемы . Не используйте NULL-ы с уникальными ключами – они не могут быть там по определению того, что является NULL и что является намерением уникального ключа. И с технической точки зрения (см. Часть о создании индекса), NULL=NULL всегда приведет к ложному – таким образом, разрешено вставлять значение NULL, если существует другое значение NULL.
Перейдите по ссылке: Как использовать уникальные индексы в MySQL и других базах данных и см. Раздел «MySQL NULLs».