Ordering Enum in MySQL - Playing with data types

Its been a day where I had to answer two MySQL string manipulation related questions. One question was from a frustrated guy who had written an application and as per him MySQL was misbehaving.

Issue was he had a table but when he tried to order an enumerated field, the result was not in alphabetical order. Try this if you haven't come across this.

1. Create a table with an enumerated column like
 CREATE table en(i INT, col ENUM('RED','YELLOW','BLUE'));

2. Insert some values into it,
insert into en values(1,'RED'), (2, 'BLUE'),(3,'RED'), (4, 'YELLOW');

3. Now try to retrieve the values ordered by the col values,
mysql> select * from en order by col asc;
+------+--------+
| i    | col    |
+------+--------+
|    1 | RED    |
|    3 | RED    |
|    4 | YELLOW |
|    2 | BLUE   |
+------+--------+

He expected the ordering to be in alphabetical order - BLUE, RED, RED and YELLOW. For him mysql had let him down. :(

So I asked him to tinker his query a bit,
mysql> select * from en order by cast(col as char);
+------+--------+
| i    | col    |
+------+--------+
|    2 | BLUE   |
|    1 | RED    |
|    3 | RED    |
|    4 | YELLOW |
+------+--------+

This forces the values from the enumerated column to be casted as characters so that the result from the operation can be sorted in alphabetical order.

We got the original result since when we define a column as enum the exact characters are not stored instead a corresponding integer value is used for storage thus optimizing the storage. You can see the corresponding integer values used by writing the following query,

mysql> select cast(col as unsigned ) from en order by col;
+------------------------+
| cast(col as unsigned ) |
+------------------------+
|                      1 |
|                      1 |
|                      2 |
|                      3 |
+------------------------+