Why shouldn't we store numbers into MySQL ENUM columns?

MySQL stores ENUM values ​​internally as integer keys (index numbers) to reference ENUM members. The main reason for not storing integer values ​​in ENUM columns is that obviously MySQL ends up referencing the index rather than the value and vice versa.

Example

The following example may clarify:

mysql> Create table enmtest(Val ENUM('0','1','2'));
Query OK, 0 rows affected (0.18 sec)

mysql> Insert into enmtest values('1'),(1);
Query OK, 2 rows affected (0.19 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> Select * from enmtest;
+-----+
| Val |
+-----+
| 1   |
| 0   |
+-----+
2 rows in set (0.00 sec)

Here, we inserted '1' as a string and accidentally also inserted a The number 1 without quotes. MySQL confusingly uses our numeric input as the index value, which is an internal reference to the first item in the member list (i.e. 0).

The above is the detailed content of Why shouldn't we store numbers into MySQL ENUM columns?. For more information, please follow other related articles on the PHP Chinese website!