Detailed explanations of ENUM types in MySQL

ENUMis a string object whose value is usually selected from a list of allowed values, which is explicitly enumerated in the column specification when the table is created.

In some of the following cases, the value can also be an empty string ( "" ) or NULL :

• If you insert an invalid value into one ENUM (that is, a string that is not in the list of allowed values), the empty string is inserted as a special error value. In fact, this string differs from a "normal" empty string because the string has a numeric index value of 0. A more detailed description is available later.
• If one ENUM is declared NULL , it NULL is also a valid value for the column, and the default value for that column will also be NULL . If one ENUM is declared NOT NULL , the default value for the column will be the first member of the value allowed for the list.

Each enumeration value has an index value:

• The member values allowed in the list value in the column description are numbered starting with 1.
• An empty string error value has an index value of 0. This means that you can use the statement shown below to SELECT find the row of records that are assigned to an invalid ENUM value.

  Mysql> SELECT * from Tbl_name WHERE enum_col=0;

• NULLThe index value of the value is NULL .

For example, specify ENUM("one", "two", "three") a column that can have any of the values shown below. The index value for each value is also as follows:

Value	Index value
NULL	NULL
""	0
"one"	1
"two"	2
"three"	3

The maximum number of different enumerations can be 65,535 member values.

Starting with MySQL 3.23.51, when the table is created, the ENUM spaces at the trailing end of the value are automatically deleted.

When assigning a ENUM value to a column, the case of the letter is irrelevant. However, the case of values retrieved later from the column is matched to the allowable values specified when the table was created.

If one is retrieved in a numeric context ENUM , the index value of the column value is returned. For example, you can use numeric values to retrieve a ENUM column like this:

Mysql> SELECT enum_col+0 from Tbl_name;

If you store a number in one ENUM , the number is treated as an index value, and the stored value is the enumeration member that corresponds to the index value. (However, this LOAD DATA will not work because it sees all the inputs as strings.) ENUM It is unwise to store numbers in a string because it can disrupt thinking.

ENUMValues are sorted according to the list order in the column specification. (In other words, the ENUM values are sorted according to their index number.) For example, for ENUM("a", "b") "a" a row "b" after, but for ENUM("b", "a") , but  "b" "a" before. An empty string is queued before a non-empty string, and the NULL value is in front of all other enumeration values. To prevent unexpected results, it is recommended to define the list in alphabetical order ENUM  . You can also use GROUP BY CONCAT(col) to determine the alphabetical order instead of the index value.

If you want to get ENUM all possible values for a column, you can use theSHOW COLUMNS FROM table_name LIKE enum_colum

Detailed explanations of ENUM types in MySQL