Enumerator (Enum) in MySQL

An ENUM is a string object whose value is decided from a set of permitted literals(Values) that are explicitly defined at the time of column creation.

Benefits of Enum data type –

• Succinct data storage required to store data in limited size columns. The strings that you pass to the enum data types implicitly get the numerical numbering.
  
• It also provides readable queries and output easily because the numbers can be translated back to the result of the corresponding string.
  

Enum syntax for columns :

CREATE TABLE table_name (
  col...
  col ENUM ('value_1','value_2','value_3', ....),
  col...
);

MySQL allows us to define the ENUM data type with the following three attributes  –

• NOT NULL – 
  If we don’t want NULL values, it is required to use the NOT NULL property in the ENUM column.
• NULL – 
  It is a synonym for DEFAULT NULL, and its index value is always NULL.
• DEFAULT – 
  By default, the ENUM data type is NULL, if the user doesn’t want to pass any value to it.
  

Example :
Suppose, we want to store the student data in the table Student_grade in order to store the grades of students in the corresponding columns (High, Medium, Low). We use the priority statement to assign the priority to the Enum column.

CREATE TABLE Student_grade(
id INT PRIMARY KEY AUTO_INCREMENT, Grade VARCHAR(250) NOT NULL,
priority ENUM('Low', 'Medium', 'High') NOT NULL
);

The prioritized column will accept only three columns. Here, the order of numbering Low->1, Medium->2, High->3.

Insert data into the table –

• Insert a new row into the table named Student_grade, the statement is as follows –

INSERT INTO Student_grade(Grade, priority)
VALUES('Good grades', 'High');

• Instead of using the enumeration values, you can also use the numerical indexes too, in order to insert the values into the Enum column of the table –

INSERT INTO Student_grade(Grade, priority)
VALUES('Poor grades', 1);
// Here we use 1 instead of using 'Low' enumeration value, 
since 1 is mapped to 'Low' implicitly.

• Let’s add more rows into the table Student_grade –

INSERT INTO Student_grade(Grade, priority)
VALUES('Mediocre grade', 'Medium');

INSERT INTO Student_grade(Grade)
VALUES('Poor grades',1);

INSERT INTO Student_grade(Grade)
VALUES('Good grades','High');

Note : ENUM column can also store NULL values if it is defined as a null-able column.

Output :

• The following statement brought all the high grades student results –

SELECT * FROM Student_grade
WHERE priority = 'High';

• The same result you can get through this My SQL query –

SELECT * FROM Student_grade
WHERE priority = 3;

• The query below selects the Student_grade and sorts them by the priority from High to Low –

SELECT Grade, priority FROM Student_grade
ORDER BY priority DESC;

Sort_data

MySQL ENUM Disadvantages :

• If you are thinking of modifying enumeration members then you need to rebuild the entire table using the ALTER TABLE command, which has quite overhead in terms of used resources and time.
  
• It is very complex to get the complete enumeration list because in that case, you need to access the inform_schema database –

SELECT colmn_type FROM inform_schema 
WHERE TABLE_NAME = 'Student_grade' AND COLUMN_NAME = 'priority';

• Porting it to other RDBMS could be a hard task because ENUM is not an SQL’s standard datatype and not many database systems provide support to it.
  
• It isn’t possible to insert more values to the enumerated column. Suppose, you are interested to insert a service-based agreement for every priority e.x., High (48hrs), Medium (4-3 days), Low (1 week), but it is not as easy as it looks and pragmatic with ENUM data type.
  
• Enumerated list is not reusable. Because if, you want to create a new table named “Emp-List” and interested to reuse its priority list, so it is not possible.