Introduction
ENUM is a string object with a value chosen from a list of permitted values defined at the time of column creation. ENUM values are represented as integers internally. Enum datatype is chosen whenever a column has a limited possible data values like gender Male or Female etc.
Benefits of ENUM data type
- Compact data storage because of its implicitly stores string as numbers
- Readable queries and output.Whenever we try to get output the numbers are translated as strings as outputs.
Syntax of Enum Data Type
CREATE TABLE table_name (
col…
col ENUM (‘value_1′,’value_2′,’value_3’, ….),
col…
);
Let’s take an example as below:
Our Objective is to have a ‘Complaint_status‘ column with possible values as ‘Pending’,’Ongoing’,’resolved’.
So we create the table with the column ‘Com_status‘ as enum and assign the proposed values.
Mysql enumeration maps the respective values with numeric indices like
Pending -> 1, ongoing -> 2, Resolved ->3
Inserting values to ENUM Column
Let us insert the row into table ‘Complaint_table’ . Execute the following statement to insert the enumeration values in the predefined list.
Now Let’s execute the following statement to insert the enumerated values using numeric indices. Both are possible to insert the values.
This example we use 2 instead of string literal,its mapping to respective string literal.
Let’s insert more values into table.
Empty and NULL Numeration values
- ENUM allowed NULL values if it is defined as a null-able column
- If strict SQL mode is enabled, Mysql does not permit the invalid value(not in predefined enum values) and empty(‘ ‘) strings and throws an error.
- If strict SQL mode is disabled,Mysql permits the invalid and empty values with the numeric index 0.
Filter and Sorting the ENUM Values
We filter the Enum values using both String literal and their respective numeric indices also.
Lets Execute the following statement to filter ‘Resolved’ complaints.
Now Filter the Complaint_status using numeric index.
The `Pending` is mapped with index value 1.
We can also sort the Enum values based on their index values.Let’s consider the following Example:
We mentioned ORDER BY DESC, so output is in Resolved ->3,ongoing -> 2, Pending -> 1 reverse order.
Drawbacks of Enumeration
- An enumeration value cannot store an expression
Example
- We cannot use a user defined variables as an enumeration value.
- For numeric values storage enum data type is not suitable because its doesn’t save on storage over the appropriate TINYINT or SMALLINT type.
Conclusion
MySQL Support various datatypes like INT , VARCHAR , CHAR , TEXT , BLOB, ENUM etc. Choosing the right data type for the right set of data is as important as the data itself. Choosing the right data type would help in optimal space allocation to specific data set, optimal space allocation to database objects etc. Schema Designing is no wonder labeled as the most critical portion of any project as the structure and its implication directly impacts and performance of the database as a whole
We @genexdb specialize in schema optimization where we have helped our customers to restructure their schema to store their data in the right data types/ Storage engines which indeed has helped them in reducing the overall database size footprint and have optimized usage of existing indexes. Please reach out to us @ support@genexdbs.com if you need help with improving your database systems. We support a wide range of database and data streaming softwares visit us genexdbs.com to learn more about us.