How Shall We Store it? – Datatypes - Database Design Manual: Using MySQL for Windows - page 31

entries from a SET column you get them as strings, they are actually stored as numeric val-
ues, thus saving storage space in the database.

ENUM

The MySQL ENUM datatype works in a similar way to the SET type described above. The
ENUM type allows each row of its column to have a value chosen from a set list which you
define at column creation time. Unlike a SET, which can have multiple elements of the set
stored in each row, ENUM allows only one or none at all. SET also has a limit of 64 elements,
but ENUM can have a maximum of 65 535 elements. You define an ENUM datatype as fol-
lows:

columnname ENUM(“Knife”,“Fork”,“Spoon”)

You will notice that this is very similar to the SET declaration. Each of the rows in a col-
umn defined as above could therefore contain only one of the following:

•

“”

•

“Knife”

•

“Fork”

•

“Spoon”

Again, this does not actually insert the string into the column, it inserts an index for the
text based on the order that the strings were declared when the column was created. So if
you were to insert “Fork” into a row, then the actual data stored would be 2, as this was the
second element that was in the list when the column was defined. However, if you retrieve
the data, MySQL realizes that the column is an ENUM type and so gives you the string
“Fork” back.
This datatype is especially useful if you are storing the responses from static dropdown
lists on webpages. As long as the contents of the dropdown list does not change, ENUM is
the perfect way to store records of what has been selected.