Tutorial by Examples

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 ... --> ...

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(na...

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?)...

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...

Examples of what happens when NULL and 'bad-value' are stored into nullable and not nullable columns. Also shows usage of casting to numeric via +0. CREATE TABLE enum ( e ENUM('yes', 'no') NOT NULL, enull ENUM('x', 'y', 'z') NULL ); INSERT INTO enum (e, enull) VA...