content_image/661889.jpg
## Creating and Using ENUM Columns 

An enumeration value must be a quoted string literal. For example, you can create a table with an ENUM column like this: 

CREATE TABLE shirts ( name VARCHAR(40), size ENUM(’x-small’, ’small’, ’medium’, ’large’, ’x-large’) ); INSERT INTO shirts (name, size) VALUES (’dress shirt’,’large’), (’t-shirt’,’medium’), (’polo shirt’,’small’); SELECT name, size FROM shirts WHERE size = ’medium’; +---------+--------+ | name | size | +---------+--------+ | t-shirt | medium | +---------+--------+ UPDATE shirts SET size = ’small’ WHERE size = ’large’; COMMIT; 

Inserting 1 million rows into this table with a value of ’medium’  would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string ’medium’  in a VARCHAR  column. 

## Index Values for Enumeration Literals 

Each enumeration value has an index: 

•The elements listed in the column specification are assigned index numbers, 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 to a position within the list of enumeration values. It has nothing to do with table indexes. 

For example, a column specified as ENUM(’Mercury’, ’Venus’, ’Earth’)  can have any of the values shown here. The index of each value is also shown. 

<img src='content_image/661889.jpg'>

An ENUM  column can have a maximum of 65,535 distinct elements. 

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 ; 

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

