MySQL supports the enum type. An enum is a unique string object in which the value is selected from a list of pre-defined values.
If you have ever used HTML, think of them as radio buttons with a set of values, and you can only select one value.
Syntax
MySQL allows us to create an enum column using the syntax shown below:
col ENUM ('value_1','value_2','value_3', ....),
);
The enum type takes three attributes:
- DEFAULT – sets the default value if the user does not select any value from a list of provided options.
- NULL – sets the default value to null.
- Not null – prevents the column from containing non-null values by forcing the user to select a value or use any default value.
Example
A classic example of the enum type is storing clothes size. Let us show how to use the enum type by creating a table that stores clothes info.
An example is as shown:
name TEXT,
SIZE ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
The above query creates a table called shirts that store the shirt’s name and the size of the enum type. In addition, the enum contains possible shirt sizes.
NOTE: Although PostgreSQL supports enum type, it requires you to define your enum values as a specific type.
An example is as shown:
Once you have the table declared with enum type, you can insert records as shown:
Once inserted, you can select specific rows using the where clause as shown:
Enum Index
Each value in an enum column is assigned an index starting from index 1. You can use this index to select, delete, or update a specific record.
For example, to select the record where the enum value is x-small using its index, we can do:
NOTE: You can also use an enum type to sort the rows.
Final
This article gives you practical usage of the enum type in SQL. Check the documentation to explore more.
