- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Questions & Answers
- MySQL ENUM column match for quoted values
- Set ENUM in MySQL for column values
- Set custom messages for enum values in MySQL
- How can I get enum possible values in a MySQL database?
- What are enumerations in Java? How to retrieve values from an enum?
- Do MySQL Can Enum type values contain spaces in it?
- How can I get enum possible values in a MySQL database using PHP?
- What is MySQL ENUM data type? What are the advantages to use ENUM data type?
- Iterating over Enum Values in Java
- what are the different attributes of MySQL ENUM data type?
- How to iterate the values in an enum in Java?
- MySQL data types int versus enum?
- Should I use MySQL enum or tinyint for fields having values 1 and 0?
- How to loop through all values of an enum in C#?
- How can we create and use ENUM columns in MySQL?