Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata