An 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.
See Section 13.3.1, “String Data Type Syntax” for
ENUM
type syntax and length
limits.
The ENUM
type 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 13.7, “Data Type Storage Requirements” for storage requirements for the
ENUM
type.Readable queries and output. The numbers are translated back to the corresponding strings in query results.
and these potential issues to consider:
If you make enumeration values that look like numbers, it is easy to mix up the literal values with their internal index numbers, as explained in Enumeration Limitations.
Using
ENUM
columns inORDER BY
clauses requires extra care, as explained in Enumeration Sorting.
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 'medium'
in a
VARCHAR
column.
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 invalidENUM
values were assigned:mysql> SELECT * FROM tbl_name WHERE enum_col=0;
The index of the
NULL
value isNULL
.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.
Value | Index |
---|---|
NULL |
NULL |
'' |
0 |
'Mercury' |
1 |
'Venus' |
2 |
'Earth' |
3 |
An ENUM
column can have a
maximum of 65,535 distinct elements.
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;
Functions such as SUM()
or
AVG()
that expect a numeric
argument cast the argument to a number if necessary. For
ENUM
values, the index number is used in
the calculation.
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
'0'
, '1'
, and
'2'
, but numeric index values of
1
, 2
, and
3
:
numbers ENUM('0','1','2')
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
'2'
. If you store '3'
,
it does not match any enumeration value, so it is treated as
an index and becomes '2'
(the value with
index 3).
mysql> 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
'enum_col
'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
C API Basic Data Structures.
An enumeration value can 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 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.If an
ENUM
column is declared to permitNULL
, theNULL
value is a valid value for the column, and the default value isNULL
. If anENUM
column is declaredNOT 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'
for
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 ORDER
BY
clause on an ENUM
column, use
one of these techniques:
Specify the
ENUM
list in alphabetic order.Make sure that the column is sorted lexically rather than by index number by coding
ORDER BY CAST(
orcol
AS CHAR)ORDER BY CONCAT(
.col
)
An enumeration value cannot be an expression, even one that evaluates to a string value.
For example, this CREATE TABLE
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
TINYINT
or
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 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.