Why should we not store a number into a MySQL ENUM column?

MySQLMySQLi Database

MySQL stores ENUM values internally as integer keys (index numbers) to reference ENUM members. The main reason for not storing the integer values in ENUM column is that it is very obvious that MySQL ends up referencing the index instead of the value and vice-versa.


Following example can clarify it −

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 1 as a number (without quotes). MySQL confusingly uses our number input as an index value i.e. an internal reference to the first item in the member list (i.e. 0).

Published on 20-Feb-2018 11:16:38