How To Use Enum Values In MySQL

The ENUM value is a valid value from a list of values. In other words, one of the values from a defined list of values forms an ENUM(or enumerated) value. MySQL allows columns to take values from a list of values. In this article, we will look into defining and usage of ENUM values in MySQL.

MySQL ENUM type

Columns in MySQL can take many different data types like integers, floating-point decimals, and string data types. Apart from the type of data that can reside in a column, MySQL can also enforce the value that can reside in the column. For example, we can restrict unique values across the columns of a table through unique indexes.

 We can also use CHECK constraints which allows for only certain values to be inserted into columns. However, the CHECK constraint works only in version 8.0.16 and onwards. For previous versions, the syntax is allowed by effectively ignored by the engine while executing queries.

 CHECK constraints can be emulated to some degree by using the SQL ENUM type in MySQL.

 An ENUM is a list of values which are explicitly enumerated. These values are attached to a column at the time of the creation of the table.

MySQL ENUM example

Let’s say we have a table that stores the weather conditions at 12 noon of the major cities. The table has a column, namely sky_condition, which can take the following values only.

• Fully Overcast
• Mostly Overcast
• Partly Overcast
• Mostly Sunny
• Bright Sunny

We can define and enforce the sky_condition column to only have these values.  If any user tries to insert a value other than one of these, an error will appear.

 In this context, the above list is an ENUM list, and every value within the list is an ENUM or ENUM value. 

We define an ENUM list at the time of table creation, and that ensures that the column can take only one of the values from the ENUM list. Let us see this with a simple example. 

Creating a table that has a column with ENUM data type

Create a table named city_weather_noon_snapshot using the following command:

CREATE TABLE city_weather_noon_snapshot (    city_id int, city_name varchar(255), day_date date, sky_condition ENUM('Fully Overcast','Mostly Overcast','Partly Overcast','Mostly Sunny','Bright Sunny'), observatory_id int );

Inserting a valid ENUM value 

Now, let’s insert a record with a valid sky_condition:

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (21, 'Vienna', current_date(),'Partly Overcast',432);

The above statement succeeds.

Inserting an invalid ENUM value  

Now, try inserting a value for sky_condition column which doesn’t exist in the ENUM list.

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date(),'Partly Sunny',1885);

Since ‘Partly Sunny’ is not a valid ENUM value here we get an error message with the following feedback:

Error Code: 1265. Data truncated for column 'sky_condition' at row 1

Even though the message is cryptic, the error indicates that we could not insert the data in the column.

Inserting a NULL value to a column with ENUM data type

However, it should be noted that we can insert NULL into the column with ENUM definition. Therefore the following statement executes without error:

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date(),null,1885);

 If we want to go ahead and does not want to have an error when inserting an invalid ENUM value for the column, we can use the INSERT IGNORE clause. The statement inserts an empty value for the ENUM column.

insert IGNORE into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date()-1,'Partly Sunny',1885);

The above statement succeeds.

Now, let’s select our city_weather_noon_snapshot table.

select * from city_weather_noon_snapshot

Result:

Figure 1. city_weather_noon_snapshot table

ENUM in SQL – things to lookout for

SQL ENUM values are an efficient way to store repeated values. At the same time, they check for the existence and enforcement of those values. However, there are some things that we need to be aware of before we start using them:

• The ENUM values are stored internally as integer values. The value stored is the ordinal or index position of the ENUM list. Therefore in an ENUM list of (‘trees’, ‘shrubs’,’herbs’), if a column takes on value ‘shrubs’, the value actually stored is 2. This is because ‘trees’ is assigned 1, ‘shrubs’ is assigned 2 and ‘herbs’ is assigned 3
• The actual conversion of these indexed values to string values happens in real-time at the time of the query. The storage of data, therefore, is more efficient.
• NULL values are stored as NULL internally as well.
• Invalid values which appear as empty values are internally assigned 0
• Since ENUM values are stored as integers the sorting of the columns in order by clause also follows the same ordinal scheme. Therefore the order is dependent on how the ENUM was defined instead of the lexicographical values. In the above ENUM, ‘trees’ sorts above ‘herbs’.
• If we insert a numeric integer and unquoted value into the ENUM column during insert statement, MySQL engine tries to insert the corresponding value of the ENUM at that index position, e.g.:

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date()-2,3 ,1885);

In the above query, we are inserting 3 (means 3rd value of the ENUM) and hence its ‘Partly Overcast’ that gets retrieved in the select query.

Figure 2. The output of the select statement

• It’s not a good practice to use ENUM values for numeric data. E.g., ENUM list (‘0′,’1′,’2’) as it doesn’t offer any storage benefit and can also result in confusion during query development.