Method for creating a unique index in MySQL with null and empty _mysql

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.