ENUM is a string object with a value chosen from a list of permitted values that
are enumerated explicitly in the column specification at table creation time. It has these advantages:
Compact data storage in situations where a column has a limited set of possible
values. The strings you specify as input values are automatically encoded as numbers. See Section 11.6, "Data Type Storage Requirements"
for the storage requirements for
Readable queries and output. The numbers are translated back to the corresponding strings in query results.
and these potential issues to consider:
An enumeration value must be a quoted string literal. For example, you can create a table with an
ENUM column like this:
CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small');SELECT name, size FROM shirts WHERE size = 'medium';+---------+--------+| name | size |+---------+--------+| t-shirt | medium |+---------+--------+UPDATE shirts SET size = 'small' WHERE size = 'large';COMMIT;
Inserting 1 million rows into this table with a value of
'medium' would require 1
million bytes of storage, as opposed to 6 million bytes if you stored the actual string
Each enumeration value has an index:
The elements listed in the column specification are assigned index numbers, beginning with 1.
The index value of the empty string error value is 0. This means that you can use
SELECT statement to find rows into which invalid
ENUM values were assigned:
SELECT * FROM
The index of the
NULL value is
The term "index" here refers to a position within the list of enumeration values. It has nothing to do with table indexes.
For example, a column specified as
ENUM('Mercury', 'Venus', 'Earth') can have any
of the values shown here. The index of each value is also shown.
ENUM column can have a maximum of 65,535 distinct elements. (The practical limit
is less than 3000.) A table can have no more than 255 unique element list definitions among its
columns considered as a group. For more information on these limits, see Section
D.10.5, "Limits Imposed by
.frm File Structure".
If you retrieve an
ENUM value in a numeric context, the column value's index is
returned. For example, you can retrieve numeric values from an
ENUM column like
Trailing spaces are automatically deleted from
ENUM member values in the table
definition when a table is created.
When retrieved, values stored into an
ENUM column are displayed using the
lettercase that was used in the column definition. Note that
ENUM columns can be
assigned a character set and collation. For binary or case-sensitive collations, lettercase is taken into
account when assigning values to the column.
If you store a number into an
ENUM column, the number is treated as the index into
the possible values, and the value stored is the enumeration member with that index. (However, this does not work with
LOAD DATA, which treats all input as strings.) If the numeric value is
quoted, it is still interpreted as an index if there is no matching string in the list of enumeration values.
For these reasons, it is not advisable to define an
ENUM column with enumeration
values that look like numbers, because this can easily become confusing. For example, the following column has
enumeration members with string values of
'2', but numeric index values of
If you store
2, it is interpreted as an index value, and becomes
'1' (the value with index 2). If you store
matches an enumeration value, so it is stored as
'2'. If you store
'3', it does not match any enumeration value, so it is treated as an index and
'2' (the value with index 3).
INSERT INTO t (numbers) VALUES(2),('2'),('3');mysql>
SELECT * FROM t;+---------+| numbers |+---------+| 1 || 2 || 2 |+---------+
To determine all possible values for an
ENUM column, use
SHOW COLUMNS FROM and parse the
tbl_name LIKE '
definition in the
Type column of the output.
In the C API,
ENUM values are returned as strings. For information about using
result set metadata to distinguish them from other strings, see Section
21.8.5, "C API Data Structures".
An enumeration value can also be the empty string (
under certain circumstances:
If you insert an invalid value into an
ENUM (that is,
a string not present in the list of permitted values), the empty string is inserted instead as a special
error value. This string can be distinguished from a "normal" empty string by the fact that this string has the numeric
value 0. See Section 11.4.4, " Index Values
for Enumeration Literals " for details about the numeric indexes for the enumeration values.
If strict SQL mode is enabled, attempts to insert invalid
result in an error.
ENUM column is declared to permit
NULL value is a valid value for the
column, and the default value is
NULL. If an
ENUM column is declared
NOT NULL, its
default value is the first element of the list of permitted values.
ENUM values are sorted based on their index numbers, which depend on the order in
which the enumeration members were listed in the column specification. For example,
'b' sorts before
'a'). The empty string sorts before nonempty strings, and
sort before all other enumeration values.
To prevent unexpected results when using the
ORDER BY clause on an
ENUM column, use one of these techniques:
ENUM list in alphabetic order.
Make sure that the column is sorted lexically rather than by index number by coding
ORDER BY CAST( or
col AS CHAR)
ORDER BY CONCAT(.
An enumeration value cannot be an expression, even one that evaluates to a string value.
For example, this
CREATE TABLE statement does not work
CONCAT function cannot be used to construct an enumeration value:
CREATE TABLE sizes ( size ENUM('small', CONCAT('med','ium'), 'large'));
You also cannot employ a user variable as an enumeration value. This pair of statements do not work:
SET @mysize = 'medium';CREATE TABLE sizes ( size ENUM('small', @mysize, 'large'));
We strongly recommend that you do not use numbers as enumeration values,
because it does not save on storage over the appropriate
SMALLINT type, and it is easy to mix up the strings and the underlying number
values (which might not be the same) if you quote the
ENUM values incorrectly. If
you do use a number as an enumeration value, always enclose it in quotation marks. If the quotation marks are
omitted, the number is regarded as an index. See Section
11.4.4, " Handling of Enumeration Literals " to see how even a quoted number could be mistakenly used as
a numeric index value.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.