GNU Info

(mysql.info)ENUM

---

Next: SET Prev: BLOB Up: String types

---

Enter node , (file) or (file)node

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.