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