The MySQL ENUM data type explained

The ENUM data type in MySQL is used to create a table column that stores data specifically enumerated (mentioned) for that column.

For example, suppose you have a help_center table that keeps a record of all cases opened by your customers.

You want to add a table called priority that indicates the importance level of the subject between low, medium, and high only.

This is an example of how you can write the CREATE TABLE statement:

CREATE TABLE help_center (
  id INT PRIMARY KEY AUTO_INCREMENT,
  subject VARCHAR(150) NOT NULL,
  priority ENUM('low', 'medium', 'high') NOT NULL
);

Notice the ENUM() syntax at the fourth line above. If you don’t want a NULL value in your ENUM column, you can set the column to NOT NULL.

You can also set one of the ENUM values as the DEFAULT value as shown below:

CREATE TABLE help_center (
  id INT PRIMARY KEY AUTO_INCREMENT,
  subject VARCHAR(150) NOT NULL,
  priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'low'
);

Each option in an ENUM type uses 1 byte of storage, so you can contain a maximum of 65,535 elements.

But I never tested the limit personally, since most of my ENUM columns only have around 5 options. It’s not recommended to add so many ENUM options anyway because it might confuse you.

When you insert a new row to the table, you can write the ENUM string value as shown below:

INSERT INTO help_center (id, subject, priority)
  VALUES (NULL, "Assist learning MySQL ENUM type", 'low');

Or you can also use the numerical index value of the option as follows:

INSERT INTO help_center (id, subject, priority)
  VALUES (NULL, "Learning MySQL ENUM index value", 1);

-- The number 1 = low, 2 = medium, 3 = high

The table will now have the following data:

+----+---------------------------------+----------+
| id | subject                         | priority |
+----+---------------------------------+----------+
|  1 | Learn MySQL ENUM type           | low      |
|  2 | Learning MySQL ENUM index value | low      |
+----+---------------------------------+----------+

Each ENUM option is stored with a numerical index that starts from 1 and ends exactly where your options end (the number 3 in the case above)

When you put an index number that’s higher than the available options, MySQL will throw an error saying data truncated as follows:

mysql> INSERT INTO help_center (id, subject, priority)
    ->   VALUES (NULL, "MySQL ENUM index out of bound", 5);
ERROR 1265 (01000): Data truncated for column 'priority' at row 1

Data truncated basically means there’s no option available for the index number that you specified.

You can also do all sorts of ordering and filtering using the ENUM column.

For the example above, you can sort the available subjects based on the priority value from high to low:

SELECT * FROM help_center
  ORDER BY priority DESC;

And here’s how to show high priority rows only:

SELECT * FROM help_center
  WHERE priority = 'high';

Using ENUM type for storing boolean values

The ENUM type can also be used to store boolean values. You need to create an ENUM column that can only store two possible values as follows:

CREATE TABLE enum_as_boolean (
  true_or_false ENUM('t', 'f') NOT NULL
);

-- 't' for 'true' and 'f' for 'false'

But since ENUM values have no internal meaning to MySQL, any option that you put into the column needs to be interpreted by the database administrators.

This means you can’t filter the table using IS TRUE or IS FALSE clause.

First, insert some data into the table as follows:

INSERT INTO enum_as_boolean 
  VALUES ('t'), ('f'), ('t'), ('f');

Then try to filter the table with the following statement:

SELECT * FROM enum_as_boolean WHERE true_or_false IS TRUE;

And you’ll see all four rows show up on the result set:

+---------------+
| true_or_false |
+---------------+
| t             |
| f             |
| t             |
| f             |
+---------------+

This is because all letters in MySQL resolve to true. You also can’t use numeric values 0 and 1 as your ENUM column options because ENUM only allows strings.

Should you use ENUM over TINYINT for boolean values? I think it’s better to use TINYINT because using ENUM creates another problem where you can’t use IS TRUE and IS FALSE in your WHERE clause.

The ENUM type is recommended for values that will not be used for filtering with boolean meaning.