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.
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.