Mysql ENUM type vs join tables

Question

My requirement

A table needs to maintain a status column.

This column represents one of 5 states.

initial design

I figured I can just make it an integer column and represent the states using a numeric value.

• 0 = start
• 1 = running
• 2 = crashed
• 3 = paused
• 4 = stopped

Since I don't want my app to maintain the mapping from the integers to their string description, I plan to place those in a separate state description table (relying on a FK relation).

Then I discovered that mysql has an ENUM type which matches my requirement exactly. Other than a direct dependency on mysql, are there any pitfalls with using the ENUM type ?

Answer 1

A table would be easier to internationalize. But so would a class outside the database entirely. Enums seem to me to be a solution in search of a problem - or what you use when all you know is databases.

Answer 2

• Changing the set of values in an ENUM requires an ALTER TABLE which causes a table restructure -- an incredibly expensive operation. Changing the set of values in a lookup table is as simple as INSERT or DELETE.

• There's no way to associate other attributes with the values in an ENUM, like which ones are retired, and which ones are eligible to be put in a drop-down list in your user interface. However, a lookup table can include additional columns for such attributes.

• It's very difficult to query an ENUM to get a list of distinct values, basically requiring you to query the data type definition from INFORMATION_SCHEMA, and parsing the list out of the BLOB returned. You could try SELECT DISTINCT status from your table, but that only gets status values currently in use, which might not be all values in the ENUM. However, if you keep values in a lookup table, it's easy to query, sort, etc.

I'm not a big fan of ENUM, as you can tell. :-)

The same applies to CHECK constraints that simply compare a column to a fixed set of values. Though MySQL doesn't support CHECK constraints anyway.

Answer 3

Here is article about speed comparison of enum. Maybe it gives some hints. IMHO it shoue be limited to a use in fixed list of strings ("Yes/No", "Child/Adult") that with 99% probability doesn't change in the future.

Answer 4

Tinyints in mysql are bad for the already explained reasons.
I can add the following fact: Enum does not ensure any kind of validation on the server side. If you insert a row with a value which does not exit in the enum definition, you'll get a nice or NULL value in the DB, depending on NULL-ability of the enum field declaration.

My point about tinyints :
- enums are limited to 65535 values
- if you don't need more than 256 values, tinyint will take less space for each row, and its behavior is much more "predictible".