ENUM is a string object with a value
chosen from a list of allowed values that are enumerated
explicitly in the column specification at table creation time.
The value may also be the empty string (
NULL under certain circumstances:
If you insert an invalid value into an
ENUM (that is, a string not present in
the list of allowed 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 numerical value 0. More
about this later.
If strict SQL mode is enabled, attempts to insert invalid
ENUM values result in an error.
ENUM column is declared to allow
is a legal value for the column, and the default value is
NULL. If an
column is declared
NOT NULL, its default
value is the first element of the list of allowed values.
Each enumeration value has an index:
Values from the list of allowable elements in the column
specification are numbered beginning with 1.
The index value of the empty string error value is 0. This
means that you can use the following
SELECT statement to find rows into which
ENUM values were assigned:
SELECT * FROM
The index of the
NULL value is
The term “index” here refers only to position
within the list of enumeration values. It has nothing to do
with table indexes.
For example, a column specified as
'three') can have any of the values shown here. The
index of each value is also shown:
An enumeration can have a maximum of 65,535 elements.
Trailing spaces are automatically deleted from
ENUM member values in the table definition
when a table is created.
When retrieved, values stored into an
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 retrieve an
ENUM value in a numeric
context, the column value's index is returned. For example, you
can retrieve numeric values from an
column like this:
If you store a number into an
the number is treated as an index, and the value stored is the
enumeration member with that index. (However, this does not work
LOAD DATA, which treats all input as
strings.) 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
ENUM values are sorted according to the order
in which the enumeration members were listed in the column
specification. (In other words,
are sorted according to their index numbers.) For example,
'a' sorts before
ENUM('a', 'b'), but
'a'). The empty string sorts before non-empty strings,
NULL values sort before all other
enumeration values. To prevent unexpected results, specify the
ENUM list in alphabetical order. You can also
GROUP BY CAST(col AS CHAR) or
GROUP BY CONCAT(col) to make sure that the
column is sorted lexically rather than by index number.
If you want to determine all possible values for an
ENUM column, use
SHOW COLUMNS FROM
and parse the
ENUM definition in the
Type column of the output.