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.
An enumeration value must be a quoted string literal; it may not
be an expression, even one that evaluates to a string value. For
example, you can create a table with an
column like this:
CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') );
However, this version of the previous
CREATE TABLE statement does
CREATE TABLE sizes ( c1 ENUM('small', CONCAT('med','ium'), 'large') );
You also may not employ a user variable as an enumeration value. This pair of statements do not work:
SET @mysize = 'medium'; CREATE TABLE sizes ( name ENUM('small', @mysize, 'large') );
If you wish to use a number as an enumeration value, you must enclose it in quotation marks. If the quotation marks are omitted, the number is regarded as an index. For this and other reasons—as explained later in this section—we strongly recommend that you do not use numbers as enumeration values.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
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 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. 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 permit
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 permitted values.
Each enumeration value has an index:
Values from the list of permissible 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
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.
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
SET columns considered as a
group. For more information on these limits, see
Section D.7.5, “Limits Imposed by .frm File Structure”.
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 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
'2', it matches an
enumeration value, so it is stored as
'3', it does not match any
enumeration value, so it is treated as an index and becomes
'2' (the value with index 3).
INSERT INTO t (numbers) VALUES(2),('2'),('3');mysql>
SELECT * FROM t;+---------+ | numbers | +---------+ | 1 | | 2 | | 2 | +---------+
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 nonempty strings,
NULL values sort before all other
enumeration values. To prevent unexpected results, specify the
ENUM list in alphabetic order. You can also
ORDER BY CAST( or
CONCAT( to make sure
that the column is sorted lexically rather than by index number.
To determine all possible values for an
COLUMNS FROM and parse the
ENUM 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 20.6.5, “C API Data Structures”.