An 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 ('')
or 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.
If an ENUM column is declared to allow
NULL, the NULL value
is a legal 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 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
invalid ENUM values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
The index of the NULL value is
NULL.
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 ENUM('one', 'two',
'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 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 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 this:
mysql> SELECT enum_col+0 FROM tbl_name;
If you store a number into an ENUM column,
the number is treated as an index, 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.) 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 '0', '1',
and '2', but numeric index values of
1, 2, and
3:
numbers ENUM('0','1','2')
ENUM values are sorted according to the order
in which the enumeration members were listed in the column
specification. (In other words, ENUM values
are sorted according to their index numbers.) For example,
'a' sorts before 'b' for
ENUM('a', 'b'), but 'b'
sorts before 'a' for ENUM('b',
'a'). The empty string sorts before non-empty strings,
and NULL values sort before all other
enumeration values. To prevent unexpected results, specify the
ENUM list in alphabetical order. You can also
use 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
tbl_name LIKE
enum_col and parse the
ENUM definition in the
Type column of the output.
