The `ENUM' Type
...............
An `ENUM' is a string object whose value normally is 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.
* If an `ENUM' is declared `NULL', `NULL' is also a legal value for
the column, and the default value is `NULL'. If an `ENUM' is
declared `NOT NULL', the 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 below. The index of each value is also
shown:
*Value* *Index*
`NULL' `NULL'
`""' 0
`"one"' 1
`"two"' 2
`"three"' 3
An enumeration can have a maximum of 65535 elements.
Lettercase is irrelevant when you assign values to an `ENUM' column.
However, values retrieved from the column later have lettercase
matching the values that were used to specify the allowable values at
table creation time.
If you retrieve an `ENUM' 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', 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.)
`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.
If you want to get all possible values for an `ENUM' column, you should
use: `SHOW COLUMNS FROM table_name LIKE enum_column_name' and parse the
`ENUM' definition in the second column.