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.7, “Data Type Storage Requirements” for the storage
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
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
'medium' in a
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 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 to a position within the list of enumeration values. It has nothing to do with table indexes.
For example, a column specified as
'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
SET 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
column like this:
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 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 | +---------+
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 23.8.5, “C API Data Structures”.
An enumeration value can also be the empty string
NULL under certain
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
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
ENUM values result in an error.
ENUM column is declared to permit
is a valid 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.
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
ENUM('b', 'a'). The empty string sorts before
nonempty strings, and
NULL values sort before
all other enumeration values.
To prevent unexpected results when using the
BY clause on an
ENUM column, use
one of these techniques:
ENUM list in alphabetic
Make sure that the column is sorted lexically rather than by
index number by coding
col AS CHAR)
An enumeration value cannot be an expression, even one that evaluates to a string value.
For example, this
statement does not work because the
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
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
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.