Enumerations are not used much in real data tables, but they can be used to perform better in some situations, such as replacing limited string values with enumerations. It should be noted that enumeration storage structure is a bit special, this section describes the application of enumeration and advantages.

Enumerations in MySQL are very compact, stored in 1-2 bytes depending on the number of enumerations. Enumerations internally actually use integers to represent the positions of the corresponding enumeration values, and then there is a.frm file in the table that stores the actual enumeration values. Take the following table for example:

CREATE TABLE t_enum_test (
  gender ENUM('male'.'female'.'unknown') NOT NULL
);
INSERT INTO t_enum_test(gender) VALUES('male'), ('female'), ('unknown');
Copy the code

The columns of this table can actually be used for integer calculations, and the result of the operation is returned as an integer:

SELECT gender+0 FROM t_enum_test;
Copy the code

The answer is actually 1,2,3. Therefore, if your enumeration values are meaningless numbers, such as ENUM(‘1’, ‘2’, ‘3’), this can lead to confusion, so it is recommended not to use meaningless values or numbers as enumerations. Another is that enumerations are sorted not by their string value, but by their numeric value. Such as:

SELECT gender FROM t_enum_test ORDER BY gender DESC;
Copy the code

The actual order is not unknown, male, and female in reverse order, as you might expect, but unknown, female, and male. So if you want an accurate sort, you need to specify sort by field value:

SELECT gender FROM t_enum_test 
ORDER BY FIELD(gender, 'unknown'.'female'.'male') DESC;
Copy the code

You can use DESC and ASC to specify the direction, but this direction is not the order of the enumerated values, but the transformation of the order. For example, the results returned above are male, female, and unknown. Therefore, if you want to sort by enumeration values in order, it is best to create a table with the enumeration values in ascending order.

The biggest drawback of enumerations is that the optional values are fixed, and adding or subtracting enumerations requires an ALTER TABLE operation. Therefore, enumerations are not appropriate if the selected string changes frequently. Of course, after MySQL 5.1, adding an enumeration value to the end of an enumeration does not rebuild the entire table. Also, because MySQL uses integer types to store enumerations, each query needs to be converted to the corresponding string value, which incurs some additional overhead. This overhead is usually influenced by the number of enumerations. However, the advantage of enumerated values is also obvious, as the table column storage space using enumerated values is smaller than that of strings.

In practice, there is a more practical approach, is to store enumeration values as integers in the data table, and then implement an enumeration lookup table similar to MySQL in the application. In this way, the process of reference enumeration in MySQL can be reduced, and the performance is also higher, but the readability of the data table will be worse.