Additional languages
« 11.4.3 The BLOB and TEXT Types
11.4.5 The SET Type »
Section Navigation      [Toggle]

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.

An enumeration value must be a quoted string literal; it may not be an expression, even one that evaluates to a string value. This means that you also may not employ a user variable as an enumeration value.

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 strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

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

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

Value Index
NULL NULL
'' 0
'one' 1
'two' 2
'three' 3

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 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. Before MySQL 4.1.1, lettercase is irrelevant when you assign values to an ENUM column. As of 4.1.1, ENUM columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase does matter when you assign 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 non-empty strings, and NULL values sort before all other enumeration values. If you expect sorting to be done alphabetically, you should specify the ENUM list in alphabetical order. You can also use GROUP BY CAST(col AS CHAR) 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 Type column of the output.


User Comments

Posted by [name withheld] on May 17 2002 2:24am[Delete] [Edit]

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.

Posted by Willem-Jan van Dinter on May 24 2003 2:14pm[Delete] [Edit]

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.

Posted by Ricky Chan on October 1 2003 12:33am[Delete] [Edit]

Be careful of expressions to match against the integer values,
Hudson's example:
SELECT * FROM classlist WHERE maxgrade <= 2
Could be redone as:
SELECT * FROM classlist WHERE maxgrade+0 <= 2
Which forces maxgrade to become a number first altough if you had maxgrade as index, it won't be used as it has to eval each maxgrade prior to test.

Similarly if you did .. where maxgrade in (1,3) this will do string comparison.. better to do either:

maxgrade = 1 or maxgrade = 3 (possibly using index)
maxgrade in ('value1','value3') (possibly using index)
maxgrade+0 in (1,3) (index.. no chance!)

Posted by syed aly on January 7 2004 12:39am[Delete] [Edit]

If you want to change the column type from enum to varchar then you can create a new table with the same structure and with the enum column redefined as varchar.
Then select * from first_table insert into second_table
will bring your data into the second table
Drop the first_table and
Rename the second_table to first_table

Posted by Sven Neuhaus on January 23 2004 6:54am[Delete] [Edit]

Thanks Willem-Jan.
Here's example code for perl to get possible values of an enum column:
$dbh->{FetchHashKeyName} = "NAME_lc";
$x = $dbh->selectrow_hashref("SHOW COLUMNS FROM tablename LIKE ?", undef, 'columnname');
$x->{type} =~ s/^enum\('//;
$x->{type} =~ s/'\)$//;
@fields = split /','/, $x->{type};

Posted by Philippe Poelvoorde on July 7 2004 1:24pm[Delete] [Edit]

e.g. char -> enum
ALTER TABLE my_table MODIFY my_col ENUM('K','X') NOT NULL;

Posted by J. Santiago Scarfo on March 31 2005 5:16pm[Delete] [Edit]

I wrote this php function to return an array from enum values, using the preg() solution posted by Willem-Jan van Dinter above, hope someone finds it useful
Note: returns array(0 => 'None') if no results or no such table

function enum($object) {
list($table, $col) = explode(".", $object);
$row=@mysql_fetch_assoc(mysql_query("SHOW COLUMNS FROM ".$table." LIKE '".$col."'"));
return ($row ? explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2",$row['Type'])) : array(0=>'None'));
}

$optarray = enum("table.column");

Posted by Peter Rekdal Sunde on May 23 2005 1:36am[Delete] [Edit]

Here is an working example that ensures you that you will get the values inside a enum field in your mysql table.

function enum($table, $field)
{
$result = @mysql_query("show columns from {$table} like \"$field\"");
$result = @mysql_fetch_assoc($result);
if($result["Type"])
{
preg_match("/(enum\((.*?)\))/", $result["Type"], $enumArray);
$getEnumSet = explode("'", $enumArray["2"]);
$getEnumSet = preg_replace("/,/", "", $getEnumSet);
$enumFields = array();
foreach($getEnumSet as $enumFieldValue)
{
if($enumFieldValue)
{
$enumFields[] = $enumFieldValue;
}
}
return $enumFields;
}
return "Unable to get enum FIELD {$field} from table {$table}";
}

// Returns an array set of all values of a enum field.
print_r(enum("MY_TABLE_NAME","MY_FIELD_NAME"));

Posted by Mark Sweeting on August 30 2005 4:21pm[Delete] [Edit]

This is an extremely simple way to get the options from an enum into an array, here called $arryEnum

<?php
$result=mysql_query("SHOW COLUMNS FROM <table> LIKE '<column>'");
if( mysql_num_rows( $result ) > 0 )
{
   $row=mysql_fetch_row($result);
   preg_match_all("/'(.*?)'/", $row['Type'], $matches);
   $arryEnum= $matches[1];
}
?>

The important bit is the regexp, which just matches anything in apostrophes.

Posted by Jan Eden on September 28 2005 11:02am[Delete] [Edit]

Hi,

since Perl and PHP have been covered, here's the Python way to get the values:

def EnumValues(dbh, column):
dbh.execute("SHOW COLUMNS FROM pages LIKE '%s'" % (column,))
column_definition = dbh.fetchone()
values = re.findall("'(.+?)'", column_definition['Type'])
return values

This works if MySQLdb.cursors.DictCursor is used as the connection's cursorclass (and if dbh is a cursor object for that connection). Refer to the index of the Type column otherwise.

Careful: Mandatory indentation has been removed by the documentation system.

Posted by Fabio Venuti on February 10 2006 3:45pm[Delete] [Edit]

Strict modes - to change the behaviour of MySQL in response to an attempt to insert invalid ENUM values - seem to be available only starting from MySQL 5.
Section "5.2.5. The Server SQL Mode" for MySQL 4.1 does not mention strict modes and indeed strict modes as defined in MySQL 5 do not work in 4.1.
Regards,

Fabio Venuti

Posted by Eric Gingell on February 18 2006 11:50pm[Delete] [Edit]

If you do ENUM('0','1','2') the value / index table looks like this:

Value / Index
NULL / NULL
' ' / 0
'0' / 1
'1' / 2
'2' / 3

Quotes are very important when using ENUM

If you do this SELECT * FROM `table` WHERE `field` = 1, you'll get all the rows where `field` = '0'.

If you do this SELECT * FROM `table` WHERE `field` = '1', you'll get all rows where `field` = '1'.

Posted by Michael Newton on March 9 2006 4:54pm[Delete] [Edit]

Note that in MySQL 5's strict mode, if you declare a column NOT NULL, you are forced to specify a value for it:

mysql> CREATE TABLE myenum (col ENUM('a', 'b', 'c', 'd') NOT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO myenum VALUES (NULL);
ERROR 1048 (23000): Column 'col' cannot be null

mysql> INSERT INTO myenum VALUES ('');
ERROR 1265 (01000): Data truncated for column 'col' at row 1

mysql> INSERT INTO myenum VALUES (0);
ERROR 1265 (01000): Data truncated for column 'col' at row 1

mysql> INSERT INTO myenum VALUES ();
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM myenum;

+-----+
| col |
+-----+
| a   |
+-----+
1 row in set (0.00 sec)


You can get around this by explicitly allowing an empty value:

mysql> CREATE TABLE myenum (col ENUM('', 'a', 'b', 'c', 'd') NOT NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO myenum VALUES ('');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM myenum;
+-----+
| col |
+-----+
|     |
+-----+
1 row in set (0.00 sec)

Posted by Clifford Hill on September 20 2006 7:58pm[Delete] [Edit]

In MySQL 5.0, you can convert an enum's values into a dynamically-defined table of values, which then provides effectively a language-neutral method to handle this kind of conversion (rather than relying on PHP, Tcl, C, C++, Java, etc. specific code).

------------------------------------------------------------

DELIMITER $$

CREATE PROCEDURE `GetEnumChoiceList`(IN dbName VARCHAR(80), IN tableName VARCHAR(80), IN columnName VARCHAR(80))
BEGIN
-- this translates an enum's choices into a single-column table.
DECLARE subQuery TEXT;
DECLARE firstPos INT(11);

-- This gets a string like "enum('value1','value2','value3')"
SET subQuery = (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA = dbName AND TABLE_NAME = tableName AND COLUMN_NAME = columnName));

-- trim off the leading "enum("
SET subQuery = SUBSTRING_INDEX(subQuery, "enum(", -1);

-- and the trailing ")"
SET subQuery = SUBSTRING_INDEX(subQuery, ")", 1);

-- replace all the "," with " UNION SELECT "
SET subQuery = REPLACE(subQuery,","," UNION SELECT ");

-- insert the first "SELECT "
SET subQuery = INSERT(subQuery,1,0,"SELECT ");

-- find the first position of "UNION"
SET firstPos = INSTR(subQuery, 'UNION');

-- insert the column name "Options" before that first "UNION"
SET subQuery = INSERT(subQuery, firstPos, 0, "AS `Options` ");

-- This is to execute the query. Until I figure out a better way.
SET @enumProcQuery = (subQuery);
PREPARE STMT FROM @enumProcQuery;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END$$

DELIMITER ;

------------------------------------------------------------

So, for example:

If you have an enumeration field called 'FieldA', in a Table called 'Table1', in a database 'DbExample', ehere the enumeration's type is:

enum('value1','value2','value3','value4')

Executing the procedure above as:

CALL GetEnumChoiceList('DbExample','Table1','FieldA');

Will return the following:

| Options |
+---------+
| value1  +
+---------+
| value2  +
+---------+
| value3  +
+---------+
| value4  +
+---------+

Where "Options" is the column name.

This works by making the set of enum values into a series of UNION SELECT statements, and then converting to a prepared statement (since I don't know how to execute the data in a query directly when it is a string definition of a query statement, rather than executing the statement directly).

If anyone knows of some trick that is better than making the statement into an @ variable then using PREPARE/EXECUTE to run the generated query, I'd be quite interested.

(curiously, PREPARE will not take a local variable as its paramater).

Posted by Eric Gingell on May 11 2006 12:40pm[Delete] [Edit]

<?php
/*** Perhaps a better way to do this is like so: ***/


$result = mysql_query("SHOW COLUMNS FROM <table>");

// Makes arrays out of all ENUM type fields.
// Uses the field names as array names and skips non-ENUM fields
while( $row = mysql_fetch_row($result) )
{
   extract($row);
   if (substr($Type, 0, 4) != 'enum') continue;

   $Type = str_replace('enum', 'array', $Type);
   // Make array
   eval("$" . $Field . " = " . $Type);
}


/**
Posted by Mark Sweeting on August 30 2005 6:21pm 
----- QUOTE -----

This is an extremely simple way to get the options from an enum into an array, here called $arryEnum
 **/

$result=mysql_query("SHOW COLUMNS FROM <table> LIKE '<column>'");
if( mysql_num_rows( $result ) > 0 )
{
   $row=mysql_fetch_row($result);
   preg_match_all("/'(.*?)'/", $row['Type'], $matches);
   $arryEnum= $matches[1];
}

//The important bit is the regexp, which just matches anything in apostrophes.

?> 

Posted by Eric Gingell on May 22 2006 5:56am[Delete] [Edit]

My previous comment was not in reference to your comment, it is referring to a comment further up the page.

Posted by Martin Poirier on July 20 2006 1:00pm[Delete] [Edit]

Another way to do it with a function NOTICE : the field is past by reference !

No test Have been done yet but it's a mix from the above so it should work...

function recover_enum_set_field(&$Field)
{
$Type = $Field;
switch (substr($Field,0,4))
{
case 'enum': $Field = "enum";
break;
case 'set(': $Field = "set";
break;
default:
return false;
}

$Type = str_replace($Field, 'array', $Type);
// Make array
eval("\$Array = " . $Type . ";");
return $Array;
}

The field parameter is a string like this "enum('value1','value2','value3')" or "set('value1','value2','value3')" in other case it will return false or will make error on the Make Array part

$Dont forget that the $Field is past by reference and it will be set to 'enum' or 'set' or not be change if not of the good type. So if you need the old field type even if it's a 'set' or 'enum' just make a copy.


Posted by Brett Zamir on February 2 2007 1:34am[Delete] [Edit]

In addition to Michael Newton's comment...

One can also get the "data truncated for column XX" error with enums when one does allow NULL values, but for which one had preexisting empty strings (not NULL, but empty strings)--if one has not explicitly allowed blank strings as one of the enum values.

I got this error message when trying to make a duplicate copy of the table--the system didn't seem to mind the original table (or my import of it to another system) not having included an empty string in the enum set when the table did in fact have empty strings--that is, until I tried copying the table within phpMyAdmin (at least with the settings I had--strict mode, etc.)

Add your own comment.