Example
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:
TINYINTwill admit invalid values; whereasENUMsets 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 withTINYINTby 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 KEYsare not free.)
