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

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

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

NULL vs NOT NULL

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)
    VALUES
        ('yes', 'x'),
        ('no',  'y'),
        (NULL,  NULL),
        ('bad-value', 'bad-value');
Query OK, 4 rows affected, 3 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 3

mysql>SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1048 | Column 'e' cannot be null                  | 
| Warning | 1265 | Data truncated for column 'e' at row 4     |
| Warning | 1265 | Data truncated for column 'enull' at row 4 |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)

What is in the table after those inserts. This uses “+0” to cast to numeric see what is stored.

mysql>SELECT e, e+0 FROM enum;
+-----+-----+
| e   | e+0 |
+-----+-----+
| yes |   1 |
| no  |   2 |
|     |   0 |  -- NULL
|     |   0 |  -- 'bad-value'
+-----+-----+
4 rows in set (0.00 sec)

mysql>SELECT enull, enull+0 FROM enum;
+-------+---------+
| enull | enull+0 |
+-------+---------+
| x     |       1 |
| y     |       2 |
| NULL  |    NULL |
|       |       0 |  -- 'bad-value'
+-------+---------+
4 rows in set (0.00 sec)