All we need is an easy explanation of the problem, so here it is.
So I have a disagreement with a coworker about using enums vs lookup tables.
This very old article from 2011 and MyQL5.5 still gets referenced again and again by those not liking enums.
But we are using MySQL8.0, on databases that are not that big, like 50 to 60 tables, with 30ish enums columns.
A few tables have up to a couple millions rows, most are way below 500K rows.
Almost all enums have fewer than 10 values, and only very few have changed (once) in their life, always to add a new value at the end of the possible values.
And we never "suffered" from any disadvantages referenced by the article like storing metadata alongside it, referencing the same values from other tables, etc…
Despite what the article says at point 2., today, adding a values at the end of the list doesn’t update the whole table, but removing a value always update (or at least read) all rows.
I do understand there are two schools of though and I understand the advantages and disadvantages of both.
But is there, today on MySQL8, any actual/factual/technological reasons so **switch away from enums for the existing columns and justify all the work in both the app and db required to do so ?
Thanks for reading and your answers.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Nothing has changed between MySQL 5.5 and 8.0 to make ENUMs any more or less evil than they were before.
I think calling it "evil" is hyperbolic. A programming language feature isn’t evil. But it can be misused by developers who don’t understand its downsides. Then they run into trouble.
If the ENUM columns you have are working well for you, and your current project has not suffered from the downsides, then there is no reason to switch them.
It’s a good thing to keep the pros and cons in mind when you design new tables. Using an ENUM in one table may work fine, whereas using it in another table in the same project will run afoul of one of those issues. It depends how each table is used.
I wrote more about ENUM in a chapter of my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.
Unfortunately, I am one of those who cringe at the sight of blood, uh … ENUMs. I referenced that same article 10.5 years ago : Advantages and Disadvantages to using ENUM vs Integer types?
My only major hangup with ENUMs is the order of values declared for the ENUM type. If you keep adding new values, just don’t change the order of the values. Keep appending new values. If you rearrange the order of the values, now the evil emerges. The context of the values can go south very fast.
You could mitigate such issues by changing the datatype to VARCHAR, change the values at your discretion, and then change the datatype back to ENUM. This way, the context of the values represented by the ENUM would remain stable. I have seen a client or two do this without problems in 5.7 so I really don’t see why 8.0 would have any problems.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂