Why you should almost always not use mysql enums for a column

The love of using enums

While designing databases for SQL(mySQL in particular) in the early years I came across the enum column type - I can’t mention how much I tried to overuse the enum type. I saw it as a more powerful boolean and I just couldn’t wait to use it with any column - say, a shiny object syndrome or every problem being a nail when you have a hammer.

Coming to hate enums

With the excessive use eventually wall was hit. The hate started when I made an update to store one more value in the enums to support a business case. Now I cluelessly added it to the beginning - this made all the rest shift their indexes and the reliability of the data was compromised.

It only took 3 hours after the update for the issues to start being reported of wrong data being displayed. In this case I used enums to store user roles - now someone else was seeing only what the admin was supposed to see.

Now this is one of the lessons I have learnt about knowing how to use tools at your disposal and in this case I messed up. However, thank God for hosting provided backups. I restored one and set out to check what actually happens with enums (because I apparently used it but didn’t know how it worked in my overuse, hehe)

One thing which particularly struck me is that internally enums are just stored as integers, so to me this beat its reason of use. Because I could as well handle the logic for defining these numbers and have one less database migration I’d need to make in case of such an update as described earlier.

I developed a list of disadvantages mainly grouped into design and maintenance

Design disadvantages

1. It tightly couples schema and data: ENUM blurs the line between data and metadata. The possible values are hardcoded into the database schema (the column definition) rather than being stored as data in a separate table.
2. Poor Portability: ENUM is not a standard ANSI SQL type and is only supported by a few RDBMSs, each with its own implementation nuances (e.g., Oracle and SQL Server do not support it). Using it can make migrating your database to another system difficult.
3. Limited to 65,535 Values: While seemingly large, this limit can be an issue for open-ended or dynamic sets of values - so imagine adding one more each time if they are more than that it would be a headache maintaining it.
4. Ambiguous Handling of Numbers and Invalid Input: MySQL has confusing implicit casting behaviors when inserting numbers or invalid strings into an ENUM column. Depending on the SQL mode, it might insert an empty string or the corresponding index number's value without a clear error, making debugging difficult

Maintenance Issues

1. Altering Values is Expensive: Adding, removing, or reordering values in a MySQL ENUM requires an ALTER TABLE statement, which can be an incredibly expensive operation on large tables, often causing table locks and potential downtime.
2. Difficult to Rename/Drop Values: There is no simple way to rename or drop an enum element; it typically involves a complex process of creating a new enum, migrating data, and dropping the old one.
3. Order Matters: ENUM values are stored internally as integer indices corresponding to the order they were defined. An ORDER BY clause will sort by this internal integer index, not the string value itself, which can lead to unpredictable results if the definition order is not alphabetical.
4. Cannot Associate Metadata: You cannot easily associate additional attributes (like a description, an "active" flag, or translation keys for internationalization) with an ENUM value. A lookup table easily accommodates extra columns for such metadata.

Resolving my earlier mentioned issue

Having seen the issues caused and the potential future issues mostly with regards to maintenance, I started trying to resolve the issue at hand. I theorised that using the same concept mySQL had on just storing the integer values on the app logic.

At first, the widely suggested solution was to use a lookup table but it just didn’t sit right with me because I saw it as bloat. That is because I’d be the only one really needing access to this to maintain logic elsewhere and it also seemed like a “let me explain” table which didn’t make sense since the database holds data at rest and the logic in the app would need to explain it.

Next, I stored the values on a JSON file - which could easily be read in the whole project. To digress a litte, the whole application was developed across three programming languages - javascript(using typescript for dev), dart and PHP. After writing a few files, this felt a bit “hacky” and not one for the long run. Can’t explain much on my reasoning but I didn’t pursue this further - just didn't feel right.

Upon further look ups I realized I didn’t need to reinvent the wheel much - the solution I used ended up being enums but in the programming languages being used in the project. Thanks to one youtube video that popped up during my google searches https://www.youtube.com/watch?v=7EttvdzxY6M

Implementing the solution

In Typescript I defined the enum as follows:

One thing I liked about this was there is an eslint rule to ensure no duplicate values - the other languages ,that is PHP and Dart, had this built in.

In PHP, I defined it as below:

Last but not leadt in Dart, I defined it as follows:

Each of the implementations has a “getLabel” method because I need it to be shown on the UI at some point.

Conclusion

Enums in databases for me were enticing at first, they promise structure and readability. But in practice, they often introduce rigidity, expensive migrations, and hidden pitfalls. My lesson was clear: keep enums in the application layer, where they provide clarity without locking your schema into brittle definitions.

If you need flexibility, lookup tables or configuration files may serve you better. But if readability and strict typing are the goal, language-level enums are a powerful and maintainable choice.

Post Credits

Knowing how I came across enums and what I have done with this post - a year from now I might laugh at how naïve this post sounds, here is to more learning.