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
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 );
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
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 | +----+---------------------------------+----------+
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
For the example above, you can sort the available subjects based on the
priority value from
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
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'
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
1 as your
ENUM column options because
ENUM only allows strings.
Should you use
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
ENUM type is recommended for values that will not be used for filtering with boolean meaning.