How are MySQL ENUM values ​​sorted?

In MYSQL, we know that each ENUM value is associated with an index number. ENUM values ​​are also sorted based on their index number. Additionally, the index number depends on the order in which the enumeration members are listed in the column specification. For example, in the ENUM (‘GOOD’, ‘EXCELLENT’) column, ‘GOOD’ ranks before ‘EXCELLENT’. In other words, we can say that the index number of "GOOD" will be "1" and the index number of "EXCELLENT" will be "2".

MySQL can also store empty strings and convert null values ​​to ENUM. It sorts empty strings before non-empty strings, and NULL before empty strings. So the sort order is as follows -

## 1. NULL 2. Empty string 3 .Non-empty string

Sort order of ENUM values

强>

td>

Example

In this example we have a table "Results" ”, which contains the ENUM column “Grade”. The table contains the following values.

mysql> Select * from Result;
+-----+--------+-------+
| Id  | Name   | Grade |
+-----+--------+-------+
| 100 | Gaurav | GOOD  |
| 101 | Rahul  | POOR  |
| 102 | Rahul  | NULL  |
| 103 | Mohan  |       |
+-----+--------+-------+
4 rows in set (0.00 sec)

MySQL now returns sorted output after using the ORDER BY clause. We can observe that the output is sorted based on the index number.

mysql> Select * from result order by grade;
+-----+--------+-------+
| Id  | Name   | Grade |
+-----+--------+-------+
| 102 | Rahul  | NULL  |
| 103 | Mohan  |       |
| 101 | Rahul  | POOR  |
| 100 | Gaurav | GOOD  |
+-----+--------+-------+
4 rows in set (0.00 sec)

The above is the detailed content of How are MySQL ENUM values ​​sorted?. For more information, please follow other related articles on the PHP Chinese website!