Imagine a developer working late at night trying to complete some simple queries for a report due the next day. No matter what she does, the resulting query will just not sort properly. Is this some undiscovered MySQL bug? No wonder this software is free, MySQL is broke and can’t even perform a basic sort. It looks simple, sort the data in a table:
mysql> SELECT id, technology from Enigma;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+
To sort the records based on the technology, do an ORDER BY on column Technology:
mysql> SELECT id, technology from Enigma ORDER BY Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+
It seems that the server does not understand what I have asked, or I have not mastered the entire alphabet …
Let’s add the clause CSA (just in case!)
mysql> SELECT id, technology from Enigma ORDER BY ASC Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+
That does not seem to be any better! MySQL does not sort the data as I wish, am I going crazy? Should I change my RDBMS?
Don’t worry, a careful look at the table structure allows us to see things clearly.
mysql> SHOW CREATE TABLE enigma;
Table: enigma
Create Table: CREATE TABLE `Enigma` (
`Id` int (11) DEFAULT NULL,
`Technology` enum (‘PHP’, ‘Linux’, ‘MySQL’) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1
The column technology is of a type ENUM. The Enum field is stored as an integer, and that is the index that MySQL uses for sorting. In other words, 1 is PHP, 3 is MySQL, and 2 is Linux, so sorting does works properly, just not as expected when the Enum data field is storing character data. So how do you get the desired result? Simply force MySQL to use the value string and not index when sorting. This can be done by using either the Concat() or Cast() functions, as follows:
function concat ()
mysql> SELECT id, technology from Enigma ORDER BY concat (technology);
+——+————-+
| Id | technology |
+——+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+————-+
function cast ()
mysql> SELECT id, technology from Enigma ORDER BY cast (technology as char);
+——+————-+
| id | Technology |
+—– -+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+———– – +