MySQL Cookbook, 2nd Edition

Sorting ENUM Values

Problem

ENUMvalues don’t sort like other string columns.

Solution

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

Discussion

ENUM is considered a string data type, but ENUM values actually 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 that 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 in that definition to have numeric values from 1 to 7. To see this for yourself, create the table using the definition just shown, and then insert into it a row for each day of the week. However, to make the insertion order differ from sorted order (so that 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 | ...