当涉及的字段之一为NULL时，MySQL错误地允许重复条目 [英] MySQL falsely allowing duplicate entries when one of the fields involved is NULL

问题描述

使用InnoDB/MySQLi，我有一个简单的表:mytable.该表具有四个字段:id(主，auto_inc)，field1，field2，field3.它们都是BIGINT，除了id之外，都可以是NULL.

Using InnoDB/MySQLi, I have a simple table: mytable. The table has four fields: id (primary, auto_inc), field1, field2, field3. All of them are BIGINT and, except for id, can be NULL.

我添加了一个独特的约束，如下所示:

I have added a unique constraint like so:

ALTER TABLE mytable ADD UNIQUE INDEX(field1,field2,field3);

但是，我完全能够添加以下行，而不会产生任何错误.我希望此生成重复"错误，但不会:

However, I am perfectly able to add the following rows without any error being generated. I would like for this to generate a 'duplicate' error, but it doesn't:

INSERT INTO mytable VALUES (NULL,3,NULL)
INSERT INTO mytable VALUES (NULL,3,NULL)

仅当所有字段的值都为非NULL时，才会生成重复"错误-例如，

It only generates a 'duplicate' error if all of the fields have non-NULL values - e.g.,

INSERT INTO mytable VALUES (2,3,4)
INSERT INTO mytable VALUES (2,3,4)

即使一个(或多个)字段具有NULL值，我如何告诉MySQL生成重复"错误?

How can I tell MySQL to generate 'duplicate' errors even if one (or more) of the fields have NULL values?

以前，这是作为错误"添加到MySQL的:

This was previously added as a "bug" to MySQL: http://bugs.mysql.com/bug.php?id=25544

推荐答案

您无法比较NULL(如果您将任何与NULL进行比较，即使NULL = NULL，结果始终为FALSE)，此行为记录在 MySQL参考.

You can't compare NULL's (if you compare anything with NULL even NULL=NULL the results is always FALSE) this behavior is documented in MySQL ref.

UNIQUE索引创建约束，以使索引中的所有值 必须与众不同.如果您尝试添加带有行的新行，则会发生错误. 与现有行匹配的键值.对于所有引擎，唯一 索引允许可以包含NULL的列使用多个NULL值.

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

因此，我认为唯一的方法是定义列NOT NULL或在触发器中处理此问题.

So I think the only way is define columns NOT NULL or handle this issue in a trigger.

这篇关于当涉及的字段之一为NULL时，MySQL错误地允许重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！