Method for creating a unique index when null or null exists in mysql _ MySQL

When creating a unique index, you must note that the default values of the database are null. in this case, the database uses null as multiple duplicate values. in many cases, the default values of the database are null, however, when the program is processed, the database value is null instead of null. Note this when creating a unique index. at this time, the database will take null as multiple duplicate values, but the index creation fails, as shown in 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 |

In step 1, 70 null data records and 40 null data records are found in the database.

Step 2:

Mysql> select count (1) from User where phone is null;
+ ---------- +
| Count (1) |
+ ---------- +
| 70 |
+ ---------- +
1 row in set (0.00 sec)

After 2, the two values of null and null in the database are verified again.

Step 3:

Mysql> alter table User add constraint uk_phone unique (phone );
ERROR 1062 (23000): Duplicate entry ''for key 'UK _ phone'
At this time, the index creation prompt ''is a duplicate attribute.

Step 4: change all null values to null

Mysql> update User set phone = NULL where phone = '';
Query OK, 40 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

Created successfully. OK