The ENUM and SET Types
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.
- Sometimes you can use an ENUM column instead of conventional string types.
- An enumeration value must be a quoted string literal; it may not be an expression, even one that evaluates to a string value.
- If you wish to use a number as an enumeration value, you must enclose it in quotation marks. It is not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing.
- Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
- Each enumeration value has an index. Values from the list of permissible elements in the column specification are numbered beginning with 1.
- When retrieved, values stored into an ENUM column are displayed using the letter case that was used in the column definition.
- 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.
- ENUM field sorts by the internal integer values, not by the strings themselves. 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.
- The biggest downside of ENUM is that the list of strings is fixed, and adding or removing strings requires the use of ALTER TABLE.
- The ENUM and SET types are generally a poor choice for identifiers, though they are appropriate for holding information such as an order’s status, a product’s type, or a person’s gender.
If you want to determine all possible values for an ENUM column:
SHOW COLUMNS FROM tbl_name LIKE enum_col
