MYSQL ENUM
Why ENUM?
ENUM provides a way to provide an attribute for a row. Attributes with a small number of non-numeric options work best. Examples:
reply ENUM('yes', 'no')
gender ENUM('male', 'female', 'other', 'decline-to-state')
The values are strings:
INSERT ... VALUES ('yes', 'female')
SELECT ... --> yes female
VARCHAR as an alternative
Let's say we have
type ENUM('fish','mammal','bird')
An alternative is
type VARCHAR(20) COMENT "fish, bird, etc"
This is quite open-ended in that new types are trivially added.
Comparison, and whether better or worse than ENUM:
.(same) INSERT: simply provide the string
.(worse?) On INSERT a typo will go unnoticed
.(same) SELECT: the actual string is returned
.(worse) A lot more space is consumed
Adding a new option
ALTER TABLE tbl MODIFY COLUMN type ENUM('fish','mammal','bird','insect');
Notes
.As with all cases of MODIFY COLUMN, you must include NOT NULL, and any other qualifiers that originally
existed, else they will be lost.
.If you add to the end of the list and the list is under 256 items, the ALTER is done by merely changing the
schema. That is there will not be a lengthy table copy. (Old versions of MySQL did not have this optimization.)