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. For
example, you can create a table with an
ENUM
column like this:
CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') );
However, this version of the previous
CREATE TABLE
statement does
not work:
CREATE TABLE sizes ( 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'; CREATE TABLE sizes ( name ENUM('small', @mysize, 'large') );
If you wish to use a number as an enumeration value, you must enclose it in quotes.
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 nonempty 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.
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.
If you want to determine all possible values for an
ENUM
column, use SHOW
COLUMNS FROM
and parse the
tbl_name
LIKE
enum_col
ENUM
definition in the
Type
column of the output.
User Comments
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.
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.
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!)
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
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};
e.g. char -> enum
ALTER TABLE my_table MODIFY my_col ENUM('K','X') NOT NULL;
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");
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"));
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.
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.
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
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'.
Note that in MySQL 5's strict mode, if you declare a column NOT NULL, you are forced to specify a value for it:
1 row in set (0.00 sec)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;
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;
1 row in set (0.00 sec)
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:
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).
<?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.
?>
My previous comment was not in reference to your comment, it is referring to a comment further up the page.
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.
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.)
Note, the section regarding ordering by an ENUM column incorrectly shows the following:
" 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.
That should read:
" 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.
Here there is the pure code SQL to obtain a list of the items of a column ENUM:
SET @my_list = (SELECT `column_type` FROM `information_schema`.`columns` WHERE `table_name`='my_table' AND `column_name`='my_column');
SET @my_list = MID(@my_list, LOCATE('(', @my_list) + 1, (LOCATE(')', @my_list) - 1) - (LOCATE('(', @my_list) + 1) + 1);
Finally, @my_lista contains the list of items, each of the items separated by one ',' of other one.
If you want to select all ENUM values you can use this code in Stored Procedure:
BEGIN
DECLARE qqq INT(5);
DECLARE stype VARCHAR(127) DEFAULT 'noempty';
DROP TEMPORARY TABLE IF EXISTS eee;
CREATE TEMPORARY TABLE eee
SELECT * FROM [some table with ENUM field called 'aaa'] LIMIT 1;
TRUNCATE eee;
SET qqq = 1;
WHILE stype <> '' DO
INSERT INTO eee (id,aaa) VALUES (qqq,qqq);
SELECT aaa INTO stype FROM eee ORDER BY id DESC LIMIT 1;
SET qqq = qqq+1;
END WHILE;
SELECT id, aaa FROM eee WHERE aaa <> '';
END
We can use enum values like '1x', '2.5x' to store numbers without confusing these with enum indexes.
We can retrieve numeric values with syntax like this: "CAST(col AS CHAR)+0" (where col is an ENUM column).
A deficiency of this method is that '100x' < '2x' (string comparison instead of numeric comparison). To workaround this we can use syntax like this: "CAST(col AS CHAR)+0 < 2".
Note that none of the above solutions account for the fact that both ' and , are allowed characters in your enum value declarations. The following statement is valid:
CREATE TABLE Foo (
bar ENUM("'asdf','qwert'", "zxcv")
)
Since MySQL escapes single quotes with more single quotes, the SHOW COLUMN statement or information_schema queries will return this type definition:
enum('asdf'',''qwert','zxcv')
In this case parsing on "','" will treat the first value as two separate values, providing inaccurate data.
The obvious solution is to simply avoid defining enum values with this set of characters, but since it is possible to do, it is worth being aware of.
I found this on php.net.
function getEnumOptions($table, $field) {
$finalResult = array();
if (strlen(trim($table)) < 1) return false;
$query = "show columns from $table";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)){
if ($field != $row["Field"]) continue;
//check if enum type
if (ereg('enum.(.*).', $row['Type'], $match)) {
$opts = explode(',', $match[1]);
foreach ($opts as $item)
$finalResult[] = substr($item, 1, strlen($item)-2);
}
else
return false;
}
return $finalResult;
}
IMPORTANT:
I know it has been mentioned in the documentation, but I feel the need to reiterate it due to the high frequency of cases that I come by it in industry.
Please please DO NOT use the ENUM type to store numbers. Especially consecutive ranges.
The example "numbers ENUM('0','1','2')" is only meant to explain how it works, NOT how to use it.
If you need something like gender, don't use ENUM('0','1'). Use something like ENUM('female','male') for example.
Extremely common, is the use of the ENUM type for stats flags. Again, please don't use ENUM('-1','0','1'), ENUM('0','1'), etc. Use something like ENUM('online','offline') or one of the types actually specifically designed to store consecutive numbers (*INT).
If you really want numeric, use a TINYINT. This will always be faster than an ENUM and use less or the same space. Don't forget to document somewhere in your code what each status number represents so that you have a human readable explanation somewhere.
Until recently TINYINT was a definite best for binary flags (that can only be 0 or 1). However, if you have mysqld>=5.0.3 you may want to investigate the BIT type which [may/may not] have better [time/space] utilisation.
Add your own comment.