In many cases, the database defaults are null, but the case is that the database values are empty rather than NULL when the program is processed. Note When a unique index is created, when the database will empty as multiple duplicate values, and the index failed to create the following example:
Step 1:
Mysql> Select Phone, COUNT (1) from User Group by phone;
+-----------------+----------+
| Phone | COUNT (1) |
+-----------------+----------+
| NULL | 70 |
| | 40 |
| +86-13390889711 | 1 |
| +86-13405053385 | 1 |
Step one found 70 null data in the database with 40 empty data.
Step 2:
Mysql> Select COUNT (1) from the User where phone is null;
+----------+
| COUNT (1) |
+----------+
| 70 |
+----------+
1 row in Set (0.00 sec)
After 2 again verify that null and NULL in the database are not the same two values.
Step 3:
Mysql> ALTER TABLE User add constraint uk_phone unique (phone);
ERROR 1062 (23000): Duplicate entry ' for key ' Uk_phone '
The index hint ' is created as a duplicate attribute at this time.
Step 4: Change all null values to NULL
mysql> Update User Set phone = NULL where phone = ';
Query OK, rows affected (0.11 sec)
Rows matched:40 changed:40 warnings:0
Step 5: Create a unique index again
Mysql> ALTER TABLE User add constraint uk_phone unique (phone);
Query OK, 0 rows affected (0.34 sec)
records:0 duplicates:0 warnings:0
Create success, OK.