Ask who help to see see! Mysql enumeration enum list to delete a value why insert make mistakes?-CodePudding

Mysql> The create table chartype_enum (
-> Gender enum (' male 'and' female 'and' confidential ', 'null')
-> ) charset=utf8;
Query OK, 0 rows affected (1.76 SEC)

Mysql> Insert into chartype_enum values (' male ');
Query OK, 1 row affected (0.00 SEC)
Mysql> Insert into chartype_enum values (' confidential ');
Query OK, 1 row affected (0.00 SEC)
Mysql> Insert into chartype_enum values (' female ');
Query OK, 1 row affected (0.00 SEC)
Mysql> Insert into chartype_enum values (' remale ');
Query OK, 1 row affected, 1 warning (0.00 SEC)
Mysql> Insert into chartype_enum values (" null ");
Query OK, 1 row affected (0.00 SEC)

Mysql> Select * from chartype_enum;
+ -- -- -- -- -- -- -- -- +
| gender |
+ -- -- -- -- -- -- -- -- +
Male | |
Secret | |
Female | |
| |
| null |
+ -- -- -- -- -- -- -- -- +
5 rows in the set (0.00 SEC)

Mysql> The select gender + 0 from chartype_enum;
+ -- -- -- -- -- -- -- -- -- -- - +
| | gender + 0
+ -- -- -- -- -- -- -- -- -- -- - +
| | 1
| 3 |
| 2 |
| | 0
4 | |
+ -- -- -- -- -- -- -- -- -- -- - +
5 rows in the set (0.00 SEC)

Mysql> Alter table chartype_enum modify gender enum (' male 'and' female 'and' confidential ');
Query OK, 5 rows affected, 1 warning (1.78 SEC)
Records: 5 Duplicates: 0 Warnings: 1

Mysql> The select gender + 0 from chartype_enum;
+ -- -- -- -- -- -- -- -- -- -- - +
| | gender + 0
+ -- -- -- -- -- -- -- -- -- -- - +
| | 1
| 3 |
| 2 |
| | 0
| | 0
+ -- -- -- -- -- -- -- -- -- -- - +
5 rows in the set (0.00 SEC)

Mysql> The delete from chartype_enum where gender=0;
Query OK, 2 rows affected (0.00 SEC)

Mysql> Select * from chartype_enum;
+ -- -- -- -- -- -- -- -- +
| gender |
+ -- -- -- -- -- -- -- -- +
Male | |
Secret | |
Female | |
+ -- -- -- -- -- -- -- -- +
3 rows in the set (0.00 SEC)

- why insert numerical errors? Here is suspected remove enumerations value subscript index did not update, it is recommended that the enumeration list changes need to be careful!!!!!!
Mysql> Insert into chartype_enum values (2);
Query OK, 1 row affected (0.00 SEC)

Mysql> Insert into chartype_enum values (3);
Query OK, 1 row affected (0.00 SEC)

Mysql> Select * from chartype_enum;
+ -- -- -- -- -- -- -- -- +
| gender |
+ -- -- -- -- -- -- -- -- +
Male | |
Secret | |
Female | |
Secret | |
Female | |
+ -- -- -- -- -- -- -- -- +
5 rows in the set (0.00 SEC)

- here and insert normally again, after the strange strange
Mysql> Insert into chartype_enum values (3);
Query OK, 1 row affected (0.00 SEC)

Mysql> Select * from chartype_enum;
+ -- -- -- -- -- -- -- -- +
| gender |
+ -- -- -- -- -- -- -- -- +
Male | |
Secret | |
Female | |
Secret | |
Female | |
Secret | |
+ -- -- -- -- -- -- -- -- +
6 rows in the set (0.00 SEC