Managing & Using MySQL, 2nd Edition

Enumerations and Sets

MySQL provides two other special kinds of types. The ENUM type allows you specify (enumerate) at table creation a list of possible values that can be inserted into that field. For example, if you have a column named fruit into which you want to allow only the values apple, orange, kiwi, or banana, you would assign this column the type ENUM:

CREATE TABLE meal(meal_id INT NOT NULL PRIMARY KEY,
                  fruit ENUM('apple', 'orange', 'kiwi',
                             'banana'))

When you insert a value into that column, it must be one of the specified fruits. Because MySQL knows ahead of time which values are valid for the column, it can abstract them to some underlying numeric type. In other words, instead of storing apple in the column as a string, MySQL stores it internally as a single-byte number. However, you still refer to it as apple in a query or when you retrieve the value from MySQL. You also use apple when you call the table or view results from the table.

The MySQL ET type works in the same way, except it lets you store multiple values in a field at the same time and uses bits instead of bytes.