Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Why should we not store a number into a MySQL ENUM column?
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.
Example
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).
Updated on: 20-Jun-2020
1K+ Views
Advertisements