Does MySQL ignore null values on unique constraints?

I have an email column that I want to be unique. But I also want it to accept null values. Can my database have 2 null emails that way?

Solution 1:

Yes, MySQL allows multiple NULLs in a column with a unique constraint.

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;

Result:

x
NULL
NULL
1

This is not true for all databases. SQL Server 2005 and older, for example, only allows a single NULL value in a column that has a unique constraint.

Solution 2:

From the docs:

"a UNIQUE index permits multiple NULL values for columns that can contain NULL"

This applies to all engines but BDB.

Solution 3:

I am unsure if the author originally was just asking whether or not this allows duplicate values or if there was an implied question here asking, "How to allow duplicate NULL values while using UNIQUE?" Or "How to only allow one UNIQUE NULL value?"

The question has already been answered, yes you can have duplicate NULL values while using the UNIQUE index.

Since I stumbled upon this answer while searching for "how to allow one UNIQUE NULL value." For anyone else who may stumble upon this question while doing the same, the rest of my answer is for you...

In MySQL you can not have one UNIQUE NULL value, however you can have one UNIQUE empty value by inserting with the value of an empty string.

Warning: Numeric and types other than string may default to 0 or another default value.