MySQL 5.0 Reference Manual  /  ...  /  The ENUM Type

11.4.4 The ENUM Type

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.

An enumeration value must be a quoted string literal; it may not be an expression, even one that evaluates to a string value. For example, you can create a table with an ENUM column like this:

    name ENUM('small', 'medium', 'large')

However, this version of the previous CREATE TABLE statement does not work:

    c1 ENUM('small', CONCAT('med','ium'), 'large')

You also may not employ a user variable as an enumeration value. This pair of statements do not work:

SET @mysize = 'medium';

    name ENUM('small', @mysize, 'large')

If you wish to use a number as an enumeration value, you must enclose it in quotation marks. If the quotation marks are omitted, the number is regarded as an index. For this and other reasons—as explained later in this section—we strongly recommend that you do not use numbers as enumeration values.

Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.

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 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. More about this later.

    If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

  • If an ENUM column is declared to permit 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 permitted values.

Each enumeration value has an index:

  • Values from the list of permissible 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.

  • The term index here refers only to position within the list of enumeration values. It has nothing to do with table indexes.

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 shown.


An 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 ENUM and SET columns considered as a group. For more information on these limits, see Section C.7.5, “Limits Imposed by .frm File Structure”.

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 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 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       |

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 nonempty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetic order. You can also use ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col) to make sure that the column is sorted lexically rather than by index number.

Functions such as SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary. For ENUM values, the cast operation causes the index number to be used.

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 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 20.6.5, “C API Data Structures”.

Download this Manual
User Comments
  Posted by Bradford Henderson on April 28, 2011
Can the enum type refer directly to a table? For example, a table (streets) with 2 cols - streetID and streetName. An example would be helpful if it showed the statement used to create the enum column and then an example statement to utilize the column.
  Posted by Tim Bancroft on April 29, 2011
@Brad: What you're looking for - referential integrity - is probably best enforced by the REFERENCE or FOREIGN KEY features in SQL which isn't fully supported in most implementations of 5.1 (at least, possibly other releases, too) (See Create TABLE A TRIGGER may work, depending on what you want to do (see CREATE TRIGGER which can reference other tables by name.

When using the returned values, you reference them as a text string, unless you use the (0+<enum_col>) format above, which would return the index as an integer.

e.g SELECT <enum_col> FROM foo INTO var WHERE...
IF <enum_col> = 'enum_string_value' THEN....

So if the column is defined in the CREATE TABLE with
<enum_col> ENUM('ValueA', 'StreetB', 'CityC', ...)
it will return 'ValueA', 'StreetB' ... and so on in normal use.

Hope this helps.

***Mods - finger slipped - possible duplicate post ***
Sign Up Login You must be logged in to post a comment.