MySQL ENUM Types Are Useful For Control and Performance

mysqlenumOne of the more controversial of the various types provided by MySQL is the ENUM. If you’re learning MySQL from a beginner’s course on database programming, you’ll probably encounter the ENUM at some point and wonder what it’s good for. While some people may say it’s more trouble than it’s worth, you should still learn and consider the ENUM and what advantages it may offer you.

ENUM Definition

MySQL offers developers a variety of types for storing data. An ENUM is a type of column that will return a STRING selected from a set of allowed values specified in advance when an ENUM column is created on a table. This means that the possible values an ENUM can contain are defined at the time a table is created. This alone makes some people shy away from the ENUM, for reasons we will discuss in a few moments.

String Object

The return value of an ENUM is the STRING that is represented by a specific item defined in the ENUM. Only strings can be stored in ENUM columns.

List of Values

There are always a pre-determined set of strings that may be contained in any ENUM column. Only those strings are permitted, and any other STRING that you try to insert into an ENUM column will be ignored as an invalid value. An ENUM may technically maintain references to up to 65,535 distinct items, but generally keeping the total under 3,000 is practical for performance. Learning about MySQL database performance is an important part of becoming a seasoned MySQL developer.

Enumerated Explicitly

The list of strings allowed in an ENUM column is defined at the time that the column is added to the table. While you can modify an ENUM after creating the column, it is not something that is done while manipulating the data. This means that not only the set of values but also the order of these values is explicitly specified when the column is created.

Creating an ENUM

You create an ENUM column when you are defining a table, just by specifying the ENUM type for a given column, and listing the allowed values to be stored.

CREATE TABLE Television (
 ID INT UNSIGNED NOT NULL,
 Model ENUM('Extra Fancy', 'Colorado', 'Standard', 'The Buzz'),
 Size SMALLINT NOT NULL
);
mysql> describe Television;
+-------+------------------------------------------------------+------+-----+---------+-------+
| Field | Type                                                 | Null | Key | Default | Extra |
+-------+------------------------------------------------------+------+-----+---------+-------+
| ID    | int(10) unsigned                                     | NO   |     | NULL    |       |
| Model | enum('Extra Fancy','Colorado','Standard','The Buzz') | YES  |     | NULL    |       |
| Size  | smallint(6)                                          | NO   |     | NULL    |       |
+-------+------------------------------------------------------+------+-----+---------+-------+

Notice that each of the possible ENUM elements in the Model column is a string. While ENUMs store values in the database as integers, the values are matched agains their position in the definition of the ENUM to produce a STRING value. The value at each position in the ENUM must be a string. If you try to select an integer value from an ENUM, the result will be the string stored at that position in the ENUM. For that reason, it is generally recommended that you not use an ENUM to store strings that represent numbers.

Note that you cannot use an expression in the definition of an ENUM value. You must use the actual string you intend the ENUM value to match.

Inserting Values

To insert a value into a table with an ENUM, just pass the value as if the ENUM column were a VARCHAR column. As long as the string you pass is defined as part of the ENUM, the value will be inserted as expected.

mysql> insert into Television (`ID`, `Model`, `Size`) values('0','Extra Fancy', 32);
mysql> insert into Television (`ID`, `Model`, `Size`) values('1','Colorado', 60);
mysql> insert into Television (`ID`, `Model`, `Size`) values('2','Extra Fancy', 60);
mysql> select * from television;
+----+-------------+------+
| ID | Model       | Size |
+----+-------------+------+
| 0  | Extra Fancy | 32   |
| 1  | Colorado    | 60   |
| 2  | Extra Fancy | 60   |
+----+-------------+------+

Defaults

If you define an ENUM to permit NULL values, then any value inserted into an ENUM that does not match any of the values permitted will be assigned the value NULL. If the ENUM does not permit NULL and a value it does not recognize is inserted, the database may throw an error. This tricky behavior can be frustrating, and is something to watch out for when you encounter problems with ENUM.

mysql> insert into Television (`ID`, `Model`, `Size`) values('3','Bogus Model', 60);
ERROR 1265 (01000): Data truncated for column 'Model' at row 1

Why Use ENUM?

Although some developers avoid ENUMs because their behavior when selecting data can seem unexpected, a developer who knows how his database schema was designed will be able to take advantage of ENUMs to improve database performance and enforce business constraints.

Less Storage

Because an ENUM only permits a certain set of STRING values, it is a very efficient way to store string-based data. When adding a STRING to an ENUM, the entire STRING is represented in the database by the integer index of the STRING in the definition of the ENUM itself. For example, if an ENUM were defined to contain the strings “cat”, “dog”, and “mouse”, a developer could add a thousand “mouse” entries and each would only take the storage space of the index value for that item.

More Control

Sometimes there are business rules or set constraints that you want to enforce at the database level. For example, a company may have a policy that only red or blue tickets may be filed. In that case, an ENUM column with the values “red” and “blue” could be created, disallowing any rogue developer from adding green tickets to the system.

Problems With ENUMs

Despite the advantages of the ENUM, there are developers who have proposed eliminating the ENUM type entirely, due to the unexpected ways it can behave when someone unfamiliar with the database schema tries to use them.

Sort Order Fixed

One complaint developers have about ENUMs is that sorting on an ENUM column returns results in the order in which the elements were added to the ENUM definition, rather than in a more expected alphabetical order. For example, if an ENUM were defined to include the strings “male”, “female”, “decline to state”, selecting and sorting on that ENUM would return the “male” items first, then the “female” items, and finally the “decline to state” items. This is the reverse of what would be expected if the results were returned in alphabetical order

mysql> select * from television order by `Model`;
+----+-------------+------+
| ID | Model       | Size |
+----+-------------+------+
| 0  | Extra Fancy | 32   |
| 2  | Extra Fancy | 60   |
| 1  | Colorado    | 60   |
+----+-------------+------+

Wrong Place For Constraints

The other major argument against ENUMs is that the database is the wrong place to set business logic. When developing an application, the model used for the business logic should live in the code, not in the storage medium. This is a philosophical debate that will continue as long as there are separate professions for code development and database development. Clearly, the decision about where business logic should live is a business decision, and depends on the architecture of a project. Having the ENUM available in the database allows a wider range of potential architectures to flourish.

The ENUM may seem trivial when you first encounter it, whether you’re taking a basic MySQL programming course, or a more advanced introduction to MySQL with PHP or some other programming language. But it will be worth your time to look into how the ENUM works, and consider whether it will solve your problems.