Skip to content
1.6
Enums
1.8

The power of enums in MySQL

When working with string data in MySQL, it's essential to ensure that you're storing valid data in your table columns. One way to do this is by using enums, a special data type that allows you to specify a predefined list of allowable values for a column. In this video, we'll explore the power of enums in MySQL and how you can use them to store and manage data more effectively.

What are enums?

Enums look like strings, but under the hood, they're stored as integers. Enums give you the readability of a string with the compact data type of an integer. An enum column has a predefined list of allowable values, and any attempt to enter a value outside this list will result in an error. This feature simplifies data validation in your database.

To see how enums work, let's create an orders table with a size column declared as an ENUM. We'll pass through five different options that represent the allowable values for the column:

SQL
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
size ENUM('extra small', 'small', 'medium', 'large', 'extra large')
);
INSERT INTO orders (size) VALUES ('small'), ('medium'), ('large');

As you can see, we've inserted data into the size column using strings. However, if we perform a simple query that forces the values to be integers, we'll see that these strings are actually stored as integers under the hood:

SQL
SELECT size, size+0 FROM orders;

This query returns:

+--------+--------+
| size | size+0 |
+--------+--------+
| small | 2 |
| medium | 3 |
| large | 4 |
+--------+--------+

Benefits of enums

Using enums in MySQL has several benefits, including:

Data validation

As mentioned earlier, enums offer data validation, which helps to ensure that only valid data is entered into a column. When attempting to enter an invalid value, an error is thrown, preventing the data from being inserted into the database.

Readability

Since enums allow you to store readable values in your database, it's easier to read the stored data at a glance. You don't need to memorize integers to understand the data; the values make sense in plain English.

Compact data type

Enums are compact data types, which means they take up less storage space than other data types, such as strings. This feature makes them ideal for databases with large amounts of data.

Downsides of enums

While enums offer several benefits, there are some downsides to using them, including:

Changes to the schema

If a business requirement changes, and you need to add another option to the allowable values, you'll have to alter the schema of your table to add a new enum. While this process is not difficult, it can be inconvenient.

Ordering

When sorting data using enums, MySQL sorts by the underlying integer value rather than the actual string. While this can be useful in some cases, it can also be quite confusing, especially if you're not expecting it.

Using integer enums

Finally, it's important to note that integer enums can be confusing and should be avoided if possible. If you must use integer enums, it's best to use a TINYINT column instead.

Conclusion

Enums in MySQL are a powerful feature that can simplify database design, increase security, and improve readability. With a predefined list of allowable values for a column, the benefits of enums are clear. However, it's also important to keep in mind the limitations and potential drawbacks of using enums, such as the need to alter the schema if allowable values change. Overall, if used correctly, enums can be a valuable tool when working with string data in MySQL.

About this lesson

An ENUM is useful when you have a fixed set of items that can be stored in the column. You get the readability of a string with the storage of an integer.

05:10
Closed captioned

Meet your instructor, Aaron Francis

Developer Education

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.