ENUM In MySQL – A Complete Reference

By /
ENUM() In MySQL

ENUM in MySQL is a data type that can be used to store a list of named values. The values in an ENUM are constrained to be unique within the table.

Introduction to ENUM in MySQL

ENUM is a string value whose value is chosen from a list of permitted values that are defined at the time of column creation. It is used to define columns that store enumeration values.

It basically stores the data in the index of 1, 2, 3 … to represent the values.

Syntax of ENUM

Create Table table_name (
    ...
    col ENUM ('value1','value2','value3'),
    ...
);
Code language: SQL (Structured Query Language) (sql)

Now we will create a table named details with priority – Low, Medium, and High. For assigning priority columns ENUM is used. The code for this is-

Create Table details (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(300) NOT NULL,
    priority ENUM('Low', 'Medium', 'High') NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Here, the ENUM will map each enumeration number to an index number. So here, the priorities low, medium, and high will be mapped to 1, 2, 3 respectively.

How to Insert ENUM values in MySQL

For inserting data in the ENUM column, the enumeration values which were earlier defined in the list will be used. Let us insert some rows in the table details.

INSERT INTO details(name, priority)
VALUES('Lily', 'Medium'),
      ('John', 'High'),
      ('Peter', 'Low');  
Code language: SQL (Structured Query Language) (sql)

We can also the above code in this way-

INSERT INTO details(name, priority)
VALUES('Lily', 2),
      ('John', 3),
      ('Peter', 1); Code language: SQL (Structured Query Language) (sql)

This is also right because the priorities are mapped according to the index number.

For example, if we insert a row with no priority so by default it will be mapped to the first index number.

INSERT INTO details(name, priority)
VALUES('David');Code language: SQL (Structured Query Language) (sql)

This is because we have defined the priority as NOT NULL.

How To Query Data

Now we will query the data from the details table. The code for this is-

SELECT 
    *
FROM
    details
WHERE
    priority = 'Low';
Code language: SQL (Structured Query Language) (sql)

Output-

ENUM
ENUM

If we replace the priority with the index number then also correct output will be received. The code for this is-

SELECT 
    *
FROM
    tickets
WHERE
    priority = 2;
Code language: SQL (Structured Query Language) (sql)

Output-

ENUM 2
ENUM 2

How to Sort MySQL ENUM Values

The ENUM values are based on the index number and therefore the order of those values will depend on how they were defined in the list. We’ll use the ORDER BY clause to sort enum values in MySQL-

Select 
    name, priority
From
    details
Order By priority;
Code language: SQL (Structured Query Language) (sql)

Output-

Sorting ENUM
Sorting ENUM

The values are sorted in ascending order by default. For Descending order-

Select 
    name, priority
From
    details
Order By priority DESC;Code language: SQL (Structured Query Language) (sql)
Sorting ENUM 2
Sorting ENUM 2

Advantages of ENUM in MySQL

  • The Enum values are iterable.
  • It stores the values in a group.
  • It reduces errors that are caused by mistyping the numbers.

Disadvantages of ENUM in MySQL

  • If we want to change the enumeration of the list, then we have to recreate the whole table which proves to be expensive in terms of time.
  • ENUM is not SQL standard and only some database systems support it which as a result proves to be difficult when porting to RDBMS.
  • You can not use another table as a priority list.
  • To get the complete enumeration list, one needs to access the information_schema database.

Conclusion

In conclusion, MySQL ENUM is a powerful data type that can be used to store a limited set of values. It is simple to use and efficient, making it a good choice for many applications. Thanks for reading! We’ve studied MySQL ENUM data type and how it is implemented. For more reference, check the official documentation of MySQL.