TINYINT as an alternative

Let’s say we have

type ENUM('fish','mammal','bird')

An alternative is

type TINYINT UNSIGNED

plus

CREATE TABLE AnimalTypes (
    type TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL  COMMENT "('fish','mammal','bird')",
    PRIMARY KEY(type),
    INDEX(name)
) ENGINE=InnoDB

which is very much like a many-to-many table.

Comparison, and whether better or worse than ENUM:

• (worse) INSERT: need to lookup the type
• (worse) SELECT: need to JOIN to get the string (ENUM gives you the string with no effort)
• (better) Adding new types: Simply insert into this table. With ENUM, you need to do an ALTER TABLE.
• (same) Either technique (for up to 255 values) takes only 1 byte.
• (mixed) There’s also an issue of data integrity: TINYINT will admit invalid values; whereas ENUM sets them to a special empty-string value (unless strict SQL mode is enabled, in which case they are rejected). Better data integrity can be achieved with TINYINT by making it a foreign key into a lookup table: which, with appropriate queries/joins, but there is still the small cost of reaching into the other table. (FOREIGN KEYs are not free.)