The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values.
MySQL ENUM data type contains the following advantages:
- Compact data storage where the column may have a limited set of specified possible values. Here, the string values automatically used as a numeric index.
- It allows readable queries and output because the numbers can be translated again to the corresponding string.
- It can accept many data types like integer, floating-point, decimal, and string.
The following are the syntax used to define ENUM data type in the column:
CREATE TABLE table_name ( Column1, Column2 ENUM ('value_1','value_2','value_3'), Column3… );
In the above syntax, we have defined only three ENUM values, but it can be increased according to our needs. Here, we have to make sure that the enumeration values should always keep inside the quoted string literal.Competitive questions on Structures in HindiKeep Watching MySQL allows us to define the ENUM data type with the following attributes:
NOT NULL: By default, the ENUM data type is NULL. If we do not want to allow the NULL values, it is required to use the NOT NULL property while specifying the ENUM column.
NULL: It is a synonym for DEFAULT NULL, and its index value is always NULL.
DEFAULT: When a value is not specified in the column, the ENUM data type inserts the default value. In other words, if the INSERT statement does not provide a value for this column, then the default value will be inserted. The DEFAULT expression does not allow to insert function. ENUM data type in My SQL includes the DEFAULT values as NULL or an empty string (”).
How do I create an enum in MySQL?
You can add a new value to a column of data type enum using ALTER MODIFY command. If you want the existing value of enum, then you need to manually write the existing enum value at the time of adding a new value to column of data type enum.
How does MySQL handle enum?
If you retrieve an ENUM value in a numeric context, the column value’s index is returned. For example, you can retrieve numeric values from an ENUM column like this: mysql> SELECT enum_col+0 FROM tbl_name; Functions such as SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary.
MySQL ENUM Example
Let us understand how ENUM data type works in MySQL with the following illustration. Here, we are going to create a table named “shirts” that contains three columns: id, name, and size. The size column uses the ENUM data type and has small, medium, large, and x-large sizes. MySQL maps these enumeration member to a numeric index where small=1, medium=2, large=3, and x-large=4 respectively. Execute the following query to create a table:
CREATE TABLE shirts ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(35), size ENUM('small', 'medium', 'large', 'x-large') );
Next, we need to insert the values into the table. During insertion, the enumeration values can be inserted either as the string literal or its numeric indexes, and both are the same. Execute the following statement to insert the values into the table:
INSERT INTO shirts(id, name, size) VALUES (1,'t-shirt', 'medium'), (2, 'casual-shirt', 3), (3, 'formal-shirt', 4), (4, 'polo-shirt', 'small');
Now, execute the SELECT statement to see the inserted values into the table:
mysql> SELECT * FROM shirts;
The following image contains all the above query results that contain the enumeration values in string literals only:
MySQL sorts the enumeration values based on their numeric indexes that depend on the order in which we have inserted the data in the column specification. For example, if we have defined the enumeration as ENUM (‘b’, ‘a’, ”, ‘c’). Then, b comes before a, the empty string comes before c (non-empty string), and NULL value comes before other values So, if we do not want to get unexpected result with ENUM data type using ORDER BY clause, follow these rules:
- Define the enumeration values in the alphabetic order.
- It is to make sure that the column name is in lexical order rather than index number.
The following example explains enumeration sorting more clearly. So, if you want to get the size of the shirts in a specific order, execute the statement below:
mysql> SELECT * FROM shirts ORDER BY size DESC;
This query will give the output as below where we can see that the size of shirts is in descending order:
Limitations of ENUM Data Type
The following are the disadvantages of ENUM data type in MySQL:
1. If we want to modify the enumeration values/ members, it can be done by rebuilding the entire table using the ALTER TABLE command. It makes expensive use of our resources and time also.
2. We cannot use an expression with enumeration members. For example, This CREATE statement does not execute because it uses CONCAT() function for creating enumeration members.
CREATE TABLE shirts ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(35), size ENUM('small', CONCAT('med','ium'), 'large', 'x-large') );
3. We cannot use the user variable as an enumeration member. It can be seen in the below example:
SET @mysize = 'large'; CREATE TABLE shirts ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(35), size ENUM('small', 'medium', @mysize, 'x-large') );
4. It is recommended that we should not use the numeric values as enumeration members.
5. It is complex to get a complete information enumeration list because we need to access the information_schema database.
6. We may face an issue during the porting of ENUM to other RDBMS because many databases do not support this data type.
7. We cannot add more attributes to the enumeration lists.
ENUM (Enumeration) Data Type in MySQL
1. MySQL ENUM is a Key/String Value Pair Type
MySQL ENUM is a key/value pair. Values are strings, and keys are index numbers.
But where’s the index?
MySQL automatically assigns numbers as they appear on your list. So, whether it’s about a shortlist of colors, customer types, salutations, or payment methods, numbers will be assigned. That is a fixed list that will never expand. Think of 20 or fewer items and values that will never have further attributes. Otherwise, you need a table.
But how are these indexes numbered?
2. MySQL ENUM Index Starts with 1 But Can Be NULL or Zero
I’ll start with an example.
CREATE TABLE people ( id int NOT NULL PRIMARY KEY AUTO_INCREMENT, lastname varchar(30) NOT NULL, firstname varchar(30) NOT NULL, middlename varchar(30) NOT NULL, gender enum('Male','Female') NOT NULL DEFAULT 'Female', country enum('United States', 'Canada', 'Brazil', 'United Kingdom','Poland','Ukraine', 'Lithuania', 'Japan','Philippines','Thailand', 'Australia','New Zealand') DEFAULT 'United States', modifieddate datetime NOT NULL DEFAULT NOW() );
There are 2 MySQL ENUM values here: gender and country. Let me start with the gender column that contains 2 values: Male and Female. The index for Male is 1, and Female is 2. This means that key indexes start with 1. From this simple example, you can identify the index for the country column. It has 12 values. It starts with the United States with an index of 1 and ends with New Zealand with an index of 12.
Note: this index doesn’t refer to the table indexes that we use for fast searches.
Besides these numbers from 1 to 65,535, ENUM columns can also be NULL or zero. In our example, the country column accepts NULL. So, aside from indexes 1 to 12, NULL is another possible index with a NULL value. You can also have a 0 index. This happens in the following situations:
- The server mode for your MySQL is not strict.
- You insert a value that is not on the list of permitted values.
- Then, the insert will succeed, but the value is an empty string with an index of zero.
To avoid errors, always use a strict server mode.
3. MySQL ENUM Limits the Possible Values in a Column
Under strict mode, the country column in our example earlier will only accept 12 possible values. So, if you try to do this, the “Data truncated for column ‘country’” error will be thrown:
INSERT INTO people (lastname, firstname, middlename, gender, country) VALUES ('Choi', 'Seungcheol', '','Male','South Korea');
The error message below occurred because South Korea is not on the enumerated list.
The error message is the same as in MySQL Workbench. If the MySQL Server used here is case-sensitive, this will not be accepted either:
INSERT INTO people (lastname, firstname, middlename, gender, country) VALUES ('Hemsworth', 'Chris', '', 'MALE', 'united states');
Why? We defined the gender as Male, not MALE. And the country is United States, not united states. In the end, enumerated values in MySQL ENUM data type act like foreign key constraints but without another table. Aside from this, there’s another benefit MySQL ENUM data provide.
4. Friendly Output Without the Use of JOIN
No need for JOINs, but the output is friendly. Let’s take the below ENUM in MySQL example to explain it:
SELECT * FROM people WHERE country = 4;
This query will retrieve people from the United Kingdom. By default, you see the strings you defined in the ENUM column. But internally, the numbered indexes are stored. Here’s the result:
Note: The data you see was generated using dbForge Studio for MySQL’s data generating tool. I generated 50,000 names using the tool.
Meanwhile, the same output can be achieved when using a separate table and a join.
SELECT p.id ,p.lastname ,p.firstname ,p.middlename ,CASE WHEN p.gender = 'M' THEN 'Male' ELSE 'Female' END AS gender ,c.countryname AS country ,p.modifieddate FROM people_no_enums p LEFT JOIN country c ON p.country = c.id WHERE p.country = 4;
So, should you use MySQL ENUM to avoid JOINs altogether? Definitely not! This is good for a small but fixed list. More data with an indefinite number of rows and more attributes requires a table. And to make a friendlier output like in Figure 2, you will also need a JOIN. Having a separate table is more flexible and requires nothing from the developer when data is live. This is not the case with Enumdatatype.
5. Filter MySQL Enumeration by Index or String Value
In point #4, you saw an example with a WHERE clause to filter with an ENUM column. It used the index to specify the country. So, this will work too:
SELECT * from people WHERE country IN (1,3,5) AND gender = 1;
You can also use the string value, like the one below:
SELECT * FROM people WHERE country='Philippines' AND gender = 'Female';
6. Sorting is by Index
Sorting can be a little tricky. ENUM values are stored according to their index numbers, not the value. Check out the code below and the output that follows in Figure 3.
SELECT DISTINCT country AS CountryName ,country + 0 AS CountryId FROM people ORDER BY country;
If you want the sorting to be based on value, cast the column to a CHAR, like the one below.
SELECT DISTINCT country AS CountryName ,country + 0 AS CountryId FROM people ORDER BY CAST(country AS char);
How about this?
SELECT DISTINCT country AS CountryName ,country + 0 AS CountryId FROM people ORDER BY CountryName;
From the looks of it, the value will be used for sorting. But that is not the case. The output will be the same as in Figure 3. ORDER BY with a CAST is the best way to sort by value.
7. MySQL ENUM Storage is Up to 2 Bytes Only
According to the official documentation, the MySQL ENUM default storage involves the index. The resulting table is more compact compared to storing the values. One (1) byte for enumerations with 1 to 255 possible values. Two (2) bytes for 256 to 65,535 possible values.
But there’s a secret I want to tell you.
Of course, when storage is concerned, the values will occupy more than the index. Since proper table design produces a smaller storage footprint, let’s create another table with a separate country table.
CREATE TABLE country ( id int NOT NULL PRIMARY KEY AUTO_INCREMENT, countryname varchar(30) NOT NULL, modifieddate datetime DEFAULT NOW() ); CREATE TABLE people_no_enums ( id int NOT NULL PRIMARY KEY AUTO_INCREMENT, lastname varchar(30) NOT NULL, firstname varchar(30) NOT NULL, middlename varchar(30) NOT NULL, gender char(1) not NULL, country tinyint DEFAULT 1, modifieddate datetime NOT NULL DEFAULT NOW() );
Now, let’s insert the same data.
INSERT INTO country (id, countryname, modifieddate) VALUES (1, 'United States', NOW()), (2, 'Canada', NOW()), (3, 'Brazil', NOW()), (4, 'United Kingdom', NOW()), (5, 'Poland', NOW()), (6, 'Ukraine', NOW()), (7, 'Lithuania', NOW()), (8, 'Japan', NOW()), (9, 'Philippines', NOW()), (10, 'Thailand', NOW()), (11, 'Australia', NOW()), (12, 'New Zealand', NOW()); INSERT INTO people_no_enums SELECT p.id ,p.lastname ,p.firstname ,p.middlename ,CASE WHEN p.gender = 1 THEN 'M' ELSE 'F' END AS gender ,c.id ,p.modifieddate FROM people p LEFT JOIN country c ON p.country = c.countryname;
To do that, we use the INFORMATION_SCHEMA.TABLES table. See the code below:
SELECT table_name, ROUND(((data_length + index_length)), 2) AS "Size in Bytes" FROM information_schema.TABLES WHERE table_schema = "testenumsdb" AND TABLE_NAME LIKE 'people%' ORDER BY (data_length + index_length) DESC;
A normalized table without ENUM columns compared to a table with it requires the same size in bytes. Both have 50,000 records of the same names using the InnoDB storage engine. But of course, the new country table will occupy space too. You need to weigh the other advantages and disadvantages of using ENUM.
8. MySQL ENUM is for String Literals Only
MySQL ENUM accepts string literals only. So, the code below won’t work:
CREATE TABLE Product ( id int NOT NULL PRIMARY KEY, productName varchar(30), color enum('red','orange',CONCAT('red','orange')) );
The CONCAT function inside the Enumdatatype is not allowed as well as other valid SQL expressions.
9. MySQL ENUM Can’t Be Reused
From this point, you’ll see the dark side of MySQL ENUM. First, you can’t reuse it. You’ll have to duplicate the same color, size, and priority enumerations if you need them in another table. A design like the one in Figure 6 below is impossible with ENUMs.
To use ENUM in the 2 tables above, you need to duplicate the priorities list on the 2 tables.
10. Adding More Values Requires Altering the Table
In the gender list earlier, we tried to use 2 items only: Male and Female. What if your company decides to embrace the LGBTQ? You need to run an ALTER TABLE and add to the end of the enumeration Lesbian, Gay, Bisexual, Transgender, and Queer. Here’s the code:
ALTER TABLE people MODIFY COLUMN gender enum('Male','Female','Lesbian','Gay','Bisexual','Transgender','Queer') NOT NULL DEFAULT 'Male';
Running this on my laptop with 50,000 records only took less than a second. Larger and more complex tables will consume a bit more time. If the gender list is a table, all you need is to insert the 5 new values. Renaming a value will also need ALTER TABLE. A separate table only requires an easy UPDATE statement.
11. You Can’t Easily List Down Possible Values
Do you populate dropdown lists or grouped radio buttons from a table? It’s easy when you have a country table. Do a SELECT id, countryname FROM country, and you’re ready to populate the dropdown list. But how will you do this with MySQL ENUM? First, get the column information from INFORMATION_SCHEMA.COLUMNS table, like this:
/* Get the possible values for country ENUM. */ SELECT TABLE_NAME ,COLUMN_NAME ,COLUMN_TYPE FROM information_schema.columns WHERE TABLE_SCHEMA='testenumsdb' AND TABLE_NAME = 'people' AND COLUMN_NAME = 'country';
Then, you must parse that string and format it before you populate a dropdown list. Quite archaic, isn’t it? but there’s one last thing.
12. MySQL ENUM is Non-Standard
ENUM is a MySQL extension to the ANSI SQL standard. Other RDBMS products do not support this. So, refer to the appropriate MySQL tutorial before starting your projects. If you need to port your MySQL database full of ENUMs to SQL Server, for example, you need to do a workaround. Workarounds will vary depending on how you design the target table in SQL Server.
You must weigh the pros and cons of using MySQL ENUM. Having a separate table with proper normalization applied is the most flexible in the uncertain future. We welcome additional points. So, head on to the Comments section below and tell us about it. You can also share this on your favorite social media platforms.Tags: