(mysql.info.gz) String types
11.4.4 The `ENUM' Type
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
* 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 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'.
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
An enumeration can have a maximum of 65,535 elements.
Starting from MySQL 3.23.51, trailing spaces are automatically deleted
from `ENUM' member values when the table is created.
Lettercase is irrelevant when you assign values to an `ENUM' column.
However, values retrieved from the column later are displayed using the
lettercase that was used in the column definition.
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 will not work with `LOAD DATA', which treats all input
as strings.) It's 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':
`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 VARCHAR)' 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 second column of the output.
(mysql.info.gz) String types
automatically generated byinfo2html