What Is Enum Data Type in MySQL?

In MySQL, an enum data type is used to define a column that can store a set of predefined values. This data type allows you to specify a list of values that the column can take, and each value is assigned an index starting from 1.

Creating an Enum Data Type

To create a column with the enum data type, you need to specify the enum keyword followed by the list of values enclosed in single quotes and separated by commas. For example:

CREATE TABLE employees (
   id INT,
   name VARCHAR(100),
   gender ENUM('male', 'female')
);

In this example, the gender column is defined with the enum data type and can only have two possible values: ‘male’ or ‘female’.

Inserting Values into Enum Columns

To insert values into an enum column, you simply need to provide one of the predefined values. For example:

INSERT INTO employees (id, name, gender)
VALUES (1, 'John Doe', 'male');

In this case, we are inserting a new row into the employees table with a gender value of ‘male’ for John Doe.

Selecting Values from Enum Columns

You can query the values stored in an enum column just like any other column in MySQL. For example:

SELECT * FROM employees WHERE gender = 'female';

This query will retrieve all rows from the employees table where the gender is ‘female’.

The Benefits of Using Enum Data Type

The enum data type offers several benefits:

Clarity: By using an enum data type, you can clearly define the possible values that a column can have. This improves the readability of your code and makes it easier for other developers to understand the intended values.
Data Validation: Enum columns provide automatic data validation.
MySQL ensures that only the predefined values are allowed to be inserted into an enum column. Any attempt to insert an invalid value will result in an error.
Efficient Storage: Enum columns are stored as a single byte, regardless of the number of possible values. This makes them more efficient in terms of storage space compared to using VARCHAR or CHAR columns.

Conclusion

The enum data type in MySQL is a useful feature for defining columns with a limited set of predefined values. It offers clarity, data validation, and efficient storage. By using enum columns, you can ensure consistent and valid data in your database tables.