mysql is null 错误,当涉及的字段之一为NULL时，MySQL错误地允许重复条目

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)

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)

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

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

解决方案

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.

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.

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