Set and enum in MySQL

Detailed explanations of ENUM types in MySQL

Enum type
An ENUM 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.

A value can also be an empty string ("") or null in some of the following cases:

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 differs from a "normal" empty string because the string has a numeric index value of 0. A more detailed description is available later.
If an ENUM is declared as Null,null is also a valid value for the column, and the default value of the column will also be NULL. If an ENUM is declared as not NULL, the default value of the column will be the first member of the allowed value 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 SELECT statement shown below to find the row of records assigned to the invalid enum value.
Mysql> SELECT * from Tbl_name WHERE enum_col=0;
The index value of the null value is null.

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

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

When assigning a value to an ENUM 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 an enum is retrieved in a numeric 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 enumeration member corresponding to the index value. (However, this will not work in LOAD DATA because it sees all the inputs as strings.) It is unwise to store numbers in an ENUM string because it can disrupt thinking.

The ENUM values are sorted according to the order of the list in the column specification description. (In other words, the ENUM values are sorted according to their index number.) For example, for an enum ("A", "B") "A" is ranked "B", but for the enum ("B", "a"), "B" is ranked before "a". An empty string is queued before a non-empty string, and the null value is before all other enumeration values. To prevent unexpected results, it is recommended to define the ENUM list in alphabetical order. You can also use GROUP by CONCAT (COL) to determine the alphabetical order instead of the index value.

If you want to get all possible values for an ENUM column, you can use SHOW COLUMNS from table_name like Enum_colum

Set,enum data types are objects of type string , where set can contain up to 64 elements and can be arbitrarily taken to the elements in the collection. An enum is an element that can only be taken to a set of wood, with a maximum of 65,536 elements, which means that set is a multiple choice and the enum is a single choice.

1MariaDB[stu149]> Create TableDb_set (2      -Set1Set('x','y','Z') not NULL,3      -ENUM1 enum (' One',' Both','three') not NULL);4Query OK,0Rows Affected (0.25sec)5 6MariaDB[stu149]> descDb_set;7 +-------+---------------------------+------+-----+---------+-------+8 |Field|Type| Null | Key | Default |Extra|9 +-------+---------------------------+------+-----+---------+-------+Ten |Set1| Set('x','y','Z')|NO|     | NULL    |       | One |Enum1|Enum' One',' Both','three')|NO|     | NULL    |       | A +-------+---------------------------+------+-----+---------+-------+ - 2Rowsinch Set(0.01sec) -  theMariaDB[stu149]> Insert  intoDb_setValues(1,3),(1,4),(4,1); -Query OK,3Rows affected,1Warning (0.09sec) -Records:3Duplicates:0Warnings:1 -  +MariaDB[stu149]> Select *  fromDb_set; - +------+-------+ + |Set1|Enum1| A +------+-------+ at |X|Three| - |X|       | - |Z|One| - +------+-------+ - 3Rowsinch Set(0.00sec) -  inMariaDB[stu149]> Insert  intoDb_setValues(2,0); -Query OK,1Row affected,1Warning (0.08sec) to  +MariaDB[stu149]> Insert  intoDb_setValues(3,2); -Query OK,1Row affected (0.05sec) the  *MariaDB[stu149]> Select *  fromDb_set; $ +------+-------+Panax Notoginseng |Set1|Enum1| - +------+-------+ the |X|Three| + |X|       | A |Z|One| the |Y|       | + |X, y|Both| - +------+-------+ $ 5Rowsinch Set(0.00sec) $  -MariaDB[stu149]> Insert  intoDb_setValues(2,6); -Query OK,1Row affected,1Warning (0.14sec) the  -MariaDB[stu149]> Select *  fromDb_set;Wuyi +------+-------+ the |Set1|Enum1| - +------+-------+ Wu |X|Three| - |X|       | About |Z|One| $ |Y|       | - |X, y|Both| - |Y|       | - +------+-------+ A 6Rowsinch Set(0.00Sec

In a set type, a binary is used to add to a range beyond what it can represent:

Set element decimal binary

' x ' 1 0001

' Y ' 2 0010

' Z ' 4 0100

The enum type exceeds the range that it can represent, and it has null values:

enym Element Index

NULL NULL

"" 0

"One" 1

"2"

"Three" 3

Set and enum in MySQL