Sorting ENUM Values
Problem
ENUM values 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 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 ...