O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required