[SQL][MySQL] sorting enum columns

sort-enum.md

ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a')

So the following SELECT result is expected:

CREATE TABLE `colors` (
  `color` ENUM('white', 'blue', 'yellow', 'orange')
);

mysql> INSERT INTO `colors` VALUES ('orange'), ('yellow'), ('blue'), ('white');

mysql> SELECT * from `colors` ORDER BY color ASC;

+--------+
| color  |
+--------+
| white  |
| blue   |
| yellow |
| orange |
+--------+

To sort by an ENUM column in alphabetical order, the easiest solution is to specify the ENUM list in alphabetical order when defining the column.

CREATE TABLE `colors` (
  `color` ENUM('blue', 'orange', 'white', 'yellow')
);

or the following:

mysql> SELECT * from `colors` ORDER BY CONCAT(color) ASC;
+--------+
| color  |
+--------+
| blue   |
| orange |
| white  |
| yellow |
+--------+

mysql> SELECT * from `colors` ORDER BY CAST(color AS CHAR) ASC;
+--------+
| color  |
+--------+
| blue   |
| orange |
| white  |
| yellow |
+--------+