Sorting ENUM Values

6.19.1 Problem

ENUM values don't sort like other string columns.

6.19.2 Solution

Learn how they work, and exploit those properties to your own advantage.

6.19.3 Discussion

ENUM is considered a string column type, but ENUM values have the special property that they are stored numerically with values ordered the same way they are listed in the table definition. These numeric values affect how enumerations are sorted, which can be very useful. Suppose you have a table named weekday containing an enumeration column day that has weekday names as its members:

CREATE TABLE weekday
(
 day ENUM('Sunday','Monday','Tuesday','Wednesday',
 'Thursday','Friday','Saturday')
);

Internally, MySQL defines the enumeration values Sunday through Saturday to have numeric values from 1 to 7. To see this for yourself, create the table using the definition just shown, then insert into it a record for each day of the week. However, to make the insertion order differ from sorted order (so you can see the effect of sorting), add the days in random order:

mysql> INSERT INTO weekday (day) VALUES('Monday'),('Friday'),
 -> ('Tuesday'), ('Sunday'), ('Thursday'), ('Saturday'), ('Wednesday');

Then select the values, both as strings and as the internal numeric value (the latter are obtained by using +0 to effect a string-to-number conversion):

mysql> SELECT day, day+0 FROM weekday;
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Monday | 2 |
| Friday | 6 |
| Tuesday | 3 |
| Sunday | 1 |
| Thursday | 5 |
| Saturday | 7 |
| Wednesday | 4 |
+-----------+-------+

Notice that because the query includes no ORDER BY clause, the records are returned in unsorted order. If you add an ORDER BY day clause, it becomes apparent that MySQL uses the internal numeric values for sorting:

mysql> SELECT day, day+0 FROM weekday ORDER BY day;
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
+-----------+-------+

What about occasions when you do want to sort ENUM values in lexical order? Force them to be treated as strings for sorting using the CONCAT( ) function. CONCAT( ) normally takes multiple arguments and concatenates them into a single string. But it can be used with just a single argument, which is useful when all you want is its behavior of producing a string result:

mysql> SELECT day, day+0 FROM weekday ORDER BY CONCAT(day);
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Friday | 6 |
| Monday | 2 |
| Saturday | 7 |
| Sunday | 1 |
| Thursday | 5 |
| Tuesday | 3 |
| Wednesday | 4 |
+-----------+-------+

If you always (or nearly always) sort a non-enumeration column in a specific non-lexical order, consider changing the column type to ENUM, with its values listed in the desired sort order. To see how this works, create a color table containing a string column and populate it with some sample rows:

mysql> CREATE TABLE color (name CHAR(10));
mysql> INSERT INTO color (name) VALUES ('blue'),('green'),
 -> ('indigo'),('orange'),('red'),('violet'),('yellow');

Sorting by the name column at this point produces lexical order because the column contains CHAR values:

mysql> SELECT name FROM color ORDER BY name;
+--------+
| name |
+--------+
| blue |
| green |
| indigo |
| orange |
| red |
| violet |
| yellow |
+--------+

Now suppose you want to sort the column by the order in which colors occur in the rainbow. (This order is given by the name "Roy G. Biv," where successive letters of that name indicate the first letter of the corresponding color name.) One way to produce a rainbow sort is to use FIELD( ):

mysql> SELECT name FROM color
 -> ORDER BY
 -> FIELD(name,'red','orange','yellow','green','blue','indigo','violet');
+--------+
| name |
+--------+
| red |
| orange |
| yellow |
| green |
| blue |
| indigo |
| violet |
+--------+

To accomplish the same end without FIELD( ), use ALTER TABLE to convert the name column to an ENUM that lists the colors in the desired sort order:

mysql> ALTER TABLE color
 -> MODIFY name
 -> ENUM('red','orange','yellow','green','blue','indigo','violet');

After converting the table, sorting on the name column produces rainbow sorting naturally with no special treatment:

mysql> SELECT name FROM color ORDER BY name;
+--------+
| name |
+--------+
| red |
| orange |
| yellow |
| green |
| blue |
| indigo |
| violet |
+--------+