How Are MySQL ENUM values sorted?

In MYSQL, as we know that each ENUM value is associated with an index number. The foundation for sorting the ENUM values is also their index number. Further, the index numbers depend on the order in which the enumeration members were listed in the column specification. For example, in ENUM (‘GOOD’, ‘EXCELLENT’) column, ‘GOOD’ sorts before ‘EXCELLENT’. In other terms 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 the empty string and null values into ENUM. It sorts empty string before nonempty string and NULL before the empty string. Hence the order of sorting would be as follows −

Sorting order of ENUM values

1. NULL

2. Empty string

3. Non-empty string

Example

In this example, we have a table ‘result’ having ENUM column ‘grade’. This table is having the following values in it.

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)

Now, after using ORDER BY clause MySQL returns the sorted output. We can observe that the output is sorted on the basis of 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)

Prabhas

Published on 20-Feb-2018 06:42:12

Previous Page Print Page