6.2.3.3 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. 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 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 here. The index of each value is also shown:

An enumeration can have a maximum of 65535 elements.

Starting from 3.23.51 trailing spaces are automatically deleted from ENUM values when the table is created.

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.) It's not advisable to store numbers in an ENUM string because it will make things confusing.

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 CONCAT(col) to make sure the column is sorted alphabetically rather than by index number.

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.

User Comments

It should be noted here that char(0) can be used
as a bool. char(0) is an empty string, but when
used with NULL you get NULL or "", perfect for
bool, and a lot smaller than enum("true","false");

Bool could use atlease a few words. Suggest valid
values.

It should be noted that although sort order works
on INDEX values rather instead of the ENUM value
(name), this is not true for comparison
operatators (<, <=, <, >= etc). These operators
appear to compare the string value of the name
instead of the index.
IE, for a table where column maxgrade is an enum
('PreK','K','1','2',3', ... '11','12','Adult'),
the expression:
SELECT * FROM classlist WHERE maxgrade <= 2
will return rows where the maxgrade is 12.
Greater-than comparisons will return true for the
non-numeric enum names, as well.

I thought the whole point of using an ENUM was as
a data integrity checking exercise. But according to
the docs above, if you insert a record with an
attribute that takes its values from an ENUM, and
the value for that attribute is bad, the record gets
inserted anyway, with an empty string (index zero)
for the ENUM attribute. How do get get mysql to
treat ENUMs as integrity constraints, i.e. not allow
inserts of data that isn't in the ENUM?

I got a pleasant surprise when I used ALTER TABLE
to modifiy the enumeration list of an ENUM field,
thereby reordering the enumeration names, so that
the numerical values associated with them changed.
It turned out that MySQL altered this field for
each record in my table, so that, even though the
numerical value for my records changed, the name
of the values remained the same. So there's no
need to hold back on this, it is safe to insert
new names, drop unused names, reorder them... with
no risk for your existing data. Thank you MySQL.

If you want to get an array in php with all possible values for an ENUM or SET column, you should use: SHOW COLUMNS FROM table_name LIKE 'enum_column_name' (with or without %-wildcards), and parse it with the following php script:

$result=mysql_query($query);
if(mysql_num_rows($result)>0){
$row=mysql_fetch_row($result);
$options=explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2",$row[1]));
}

Now $options is an array with all possible values

Hope it helps.

Add your own comment.