Mysql creates a unique index when null or null exists. mysqlnull

Mysql creates a unique index when null or null exists. mysqlnull

In many cases, the default values of the database are null, but many cases occur after program processing. The database value is null rather than 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

Unique index creation in MYSQL

Null fields cannot be indexed,

The following code is correct!

Create table 'qtable '(
'Id' INT (12) not null AUTO_INCREMENT primary key,
'Pname' VARCHAR (250) NULL,
'Pvar 'VARCHAR (20) NULL
) ENGINE = MYISAM;

Unique index creation in MYSQL

The data used for your table should contain duplicate data or duplicate null values. The unique index cannot contain duplicate values.