Detailed explanation of the ENUM type in the MySQL database

MySQL databaseBecause of work requirements, we may useENUM typeBut since this type is not very common, we may not be very familiar with it. It doesn't matter. This article will explain the ENUM type in detail, hoping to help you.

The ENUM type is a string object whose values are usually selected from a allowed value list. This list is explicitly listed in the column type description when the table is created.

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

If an invalid value is inserted into an ENUM (that is, a string that is not in the allowed value list), the Null String is inserted as a special error value. In fact, this string is different from a "normal" Null String because it has a numeric index value of 0. More details will be provided later.

If an ENUM is declared as NULL, NULL is also a valid value of this column, and the default value of this column is also NULL. If an ENUM is declared as not null, the default value of this column will be the first member of the value allowed by this list. Each enumerated value has an index value.

In the column description, the allowed Member values of the List value are numbered from 1.

The index value of the null string error value is 0. This means that you can use the SELECT statement shown below to find the record rows that are assigned an invalid ENUM value. Mysql> SELECT * FROM tbl_name WHERE enum_col = 0;

The index value of the NULL value is NULL. For example, a column specified as ENUM ("one", "two", "three") can have any value shown below. The index value of each value is also as follows: the value index value NULLNULL "" 0 "one" 1 "two" 2 "three" 3 can have a maximum of 65535 Member values. Starting from MySQL 3.23.51, when a table is created, spaces at the end of the ENUM value are automatically deleted. When an ENUM column is assigned a value, the uppercase and lowercase letters are irrelevant. However, the case sensitivity of the values retrieved from the column later matches the allowed values specified during table creation.

If you retrieve an ENUM in a digital context, the index value of the column value is returned. For example, you can use numeric values to retrieve an ENUM column like this: mysql> SELECT enum_col + 0 FROM tbl_name;

If a number is stored in an ENUM, the number is treated as an index value and the stored value is the enumerated member corresponding to the index value. (However, this will not work in load data because it regards all input as strings .) It is unwise to store numbers in an ENUM string because it may disrupt thinking.

The ENUM value is ordered according to the list order in the column type description. (In other words, ENUM values are sorted by their index numbers .) For example, for ENUM ("a", "B"), "a" is placed after "B", but for ENUM ("B", ""), "B" is placed before ". Empty strings are placed before non-empty strings, and NULL values are placed before all other enumerated values. To prevent unexpected results, we recommend that you define the ENUM list in alphabetical order. You can also use group by concat (col) to determine which values are sorted alphabetically rather than indexed.

To obtain all possible values of an ENUM column, use show columns from table_name LIKE enum_colum.

Here is an introduction to the ENUM type of the MySQL database. If you want to learn more about the MySQL database, you can refer to the article http://database.51cto.com/mysql/, which will surely bring you the harvest.