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
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 | ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.