I used to be a fan of ENUM columns in MySQL databases. They’re lighter-weight than explicit lookup tables and, I presumed, led to less expensive operations than foreign key checks on a lookup table. But I’ve changed my mind:
- Changes to the set of values in an ENUM requires an ALTER TABLE which is certainly not light-weight, and so ENUM should certainly be avoided where the value pool changes frequently.
- There’s no way to associate attributes with the values in an ENUM – for example “is_enabled” or “sort_order” meta data.
- Getting a list of all possible values for an ENUM requires a query of the information_schema and parsing the values out of the BLOB that’s returned. (A SELECT DISTINCT on the table itself will only give you a list of used values of course.)
- Most surprising of all (to me) is that MySQL does not do validation of values on the server: if you INSERT a row with a value which does not exist in the enum definition, it’ll happily insert the row but with a NULL value (if the column is nullable.) The foreign key checking afforded by the use of a lookup table is much more appealing to me.