MySQL ENUM: How To Restrict MySQL Column Values

What is MySQL ENUM? MySQL ENUM is a method of ensuring that the right values are entered into a MySQL table. It’s a beneficial data type and often underused. Frequently, it pops up in SQL interview questions.

When you create a column that uses the MySQL ENUM type, you can specify a list of permissible values for that column. By doing so, you can help ensure that all data inserted into that column is valid.

Today, we’re going to be taking a look at the MySQL ENUM function.

MySQL ENUM example: building a database of shirts

For the purposes of example, we will be building a database of shirts. Let’s say that we’re trying to manage a kid’s soccer league, and we need to know exactly what type of shirt to order.

Our table will look like this:

As you can see, our Shirt Size and Shirt Color columns could be “CHAR(25)” or “VARCHAR(25)” — characters of 25 or less. But what we don’t want to happen is this:

Do you see the typo above? If we send that off to the shirt printer, it’s possible that it will not be processed correctly. If we do this:

Maria’s shirt isn’t going to be included in that count because it’s not listed as “Medium,” but rather as “Meduim.” When it comes to databases, you can run into many issues when you don’t have the data that you expect.

What is MySQL ENUM used for?

Our preamble explains why people use MySQL ENUM. It is used when specific values can be used within a column and other values would be incorrect. ENUM enforces a list of allowed values and rejects any values that are not allowed.

The function takes a comma-separated list of values as input and creates a column in the table that can only store values from this list. You can also use the ENUM function to create a lookup table, which can be used to map values to specific columns.

It should also be noted that the ENUM function makes a MySQL table smaller, even if slightly. MySQL isn’t storing the full text each time; it’s only storing the key. So, for instance, instead of storing ‘Blue’, it would store ‘1’. Generally, this isn’t a significant difference, but it can be in a very large table.

How do you use MySQL ENUM?

If we built our “shirts” table without ENUM, we would build use the following create table statement:

In the above table, we have:

• ID. This is a primary key that operates as a unique ID and auto increments. It will start at 1 and continue to go up. 
• Name. This is a variable-sized character up to the size of 50. We don’t use this as the primary key because there could be children with the same name.
• Size. This is the size of the shirt, a variable-sized character up to the size of 25.
• Color. This is the color of the shirt, a variable-sized character up to the size of 25.

However, the above table would introduce the problems that we discussed above. It’s much safer to create a table like this:

In the above table, we introduced two values:

• Size. This is now an ENUM that has three potential values: Small, Medium, or Large.
• Color. This is now an ENUM that has three potential values: Blue, Green, or Purple.

The above table will only accept the given values or a null value for the ENUM column, which ensures the validity of the data stored in the table and in the database. 

How are ENUM values stored?

You’re probably wondering how MySQL stores ENUM values. It’s important because it affects how you’ll manipulate the data in the future.

MySQL stores ENUM data in a special way that allows for quick lookup. When you store a value in an ENUM column, MySQL actually stores the value index in the table and stores a separate list of potential values elsewhere.

This means that the database can quickly find the value associated with a given index. But that also means that when we set the SHIRTS color to ‘Blue,’ it’s not the value ‘Blue’ that will be stored. 

Usually, that doesn’t cause any problems. In fact, it won’t cause any problems unless we decide to change the values of the ENUM set — or try to complete numerical operations with ENUM values.

How do you select values from an ENUM?

You can still select values from an ENUM as normal; it will return the string that you’re looking for.

It will return the value as though it was a string. But what it’s really doing is looking at the ENUM, finding the index value, and then matching it to the corresponding ENUM chart.

It’s important to realize this because it does add some complexity to the process. In terms of raw scalability and performance, ENUMs usually don’t perform as well as other value types.

Will ENUM accept an empty string?

An ENUM column can accept an empty string, which will be treated as null for the purposes of the enumeration list. Thus, you can also test to see whether the ENUM column is null. For instance:

The above is a great way to select the name of all the children who haven’t yet requested a shirt size yet. When it comes to data storage, you need to consider use cases; if you need to check on empty columns, a null value makes sense.

Can you use multiple data types with ENUM?

Is the ENUM data type static? During the create table statement, you may have noticed that we didn’t specify an INT or CHAR. That’s because ENUM is its own data type. Each column in ENUM is a string that is then placed into the enumeration list (the list of permitted values).

But could you have an ENUM like this: ENUM(1, 4.0, ‘Three’)?

You should only use ENUM for string literals. You should not use ENUM for numeric values.

What happens if you enter the wrong value into an ENUM?

If you enter a value into an ENUM column that is not in the list of allowed values, MySQL will reject the value and throw an error. This can help you ensure that the database is accurate, but it also means that you need to prepare your code for the data being potentially rejected.

What are alternatives to MySQL ENUM?

MySQL ENUM validates values on the database end. But it’s also possible to validate values on the code end. For instance, in PHP, you could write:

In other words, you could refuse to accept any values that shouldn’t go in the database. But it’s actually safer to do this on both ends; to validate your entries within your codebase and within your database.

MySQL ENUM is a great way to ensure that data entered into your table conforms to a specific set of values. It’s much safer than simply relying on code validation, as the ENUM function will reject any incorrect values at the database level.

Another alternative to ENUM is SET. SET is used the same way as ENUM and declared the same way as ENUM, with one important difference: SET lets you select multiple values.

With our T-shirt example, we don’t want multiple values; we don’t want someone asking for a T-shirt that is both large and small and both blue and green.

But if we were asking someone for their color preferences, we might want to use SET instead. If we used SET, they would be able to select blue and green at the same time.

SET functions internally much like ENUM. Each selected item corresponds to a list. The only difference is that you can select multiple items.

What are the disadvantages to MySQL ENUM?

The main disadvantage to MySQL ENUM is that it can be a bit slower than other data types. Additionally, if you need to change the list of values that are accepted by your column, you’ll need to alter your table.

Finally, not all database engines support the ENUM function; it shouldn’t be assumed just because another database is a SQL database that ENUM is included. If you’re using a different database engine, you may need to find an alternative solution.

How do you change a MySQL ENUM?

If you need to change the list of values that are accepted by your column, you’ll need to alter your table. Let’s say we needed to change the colors from Blue, Green, and Purple to Blue, Green, and Red. We would do this:

When you alter an ENUM, all of the values in the table may be shifted. The values are represented by numbers. So keep this in mind.

How can you learn more about MySQL data types?

MySQL data types are essentially the foundation of the data system and data management within MySQL. Like ENUM, not all MySQL data types will be found in different databases. This makes it critical to learn not only SQL but MySQL specifically.If you want to learn more about MySQL, Udemy has an array of MySQL Courses that can help you expand your knowledge whether you’re a beginner or advanced learner. Also consider going to a bootcamp, reading some tutorials online, or just looking at a MySQL cheat sheet of data types. Over time, you’ll find yourself remembering more about the data types available to you.