Shulou(Shulou.com)05/31 Report--

This article is to share with you about the role of the ENUM type in the MySQL database, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What does the ENUM type mean in the MySQL database

The ENUM type is 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 an invalid value is inserted into an 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 is different from a "normal" empty string because it has a numeric index value of 0. It will be described in more detail later.

If an ENUM is declared as NULL,NULL is also a legal value for the column, and the default value for the column will also be NULL. If an ENUM is declared as NOTNULL, the default value of the column will be the first member of the allowed value of the list. Each enumerated value has an index value.

The member values allowed for list values in the column description are numbered starting with 1.

The index value of the empty string error value is 0. This means that you can use the SELECT statement shown below to find record rows assigned to invalid ENUM values. Mysql > SELECT*FROMtbl_nameWHEREenum_col=0

What does the ENUM type mean in the MySQL database

The index value of the NULL value is NULL. For example, a column specified as ENUM ("one", "two", "three") can have any of the values shown below. The index value of each value is also shown below: the value index value NULLNULL "0" one "1" two "2" three "3 can have up to 65535 member values if enumerated. Starting with MySQL3.23.51, the space at the end of the ENUM value is automatically deleted when the table is created. When assigning a value to an ENUM column, the case of the letters does not matter. However, the case of the value later retrieved from the column matches the allowable value specified when the table was created.

If you retrieve an ENUM in a numeric context, the index value of the column value will be returned. For example, you can use numeric values to retrieve an ENUM column like this: mysql > SELECTenum_col+0FROMtbl_name

If you store a number in an ENUM, the number is treated as an index value, and the stored value is the enumeration member corresponding to the index value. (however, this will not work in LOADDATA because it treats all inputs as strings.) It is unwise to store numbers in an ENUM string because it can upset your mind.

The ENUM values are sorted according to the order of the list in the column specification. (in other words, ENUM values are sorted by their index numbers.) For example, for ENUM ("a", "b"), "a" comes after "b", but for ENUM ("b", "a"), "b" comes before "a". The empty string comes before the non-empty string and the null value comes before all other enumerated values. To prevent unexpected results, it is recommended that you define the ENUM list in alphabetical order. You can also determine whether to sort alphabetically instead of indexed values by using GROUPBYCONCAT (col).

If you want to get all the possible values for an ENUM column, you can use SHOWCOLUMNSFROMtable_nameLIKEenum_colum to do so.

The above is the role of the ENUM type in the MySQL database. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.