Recipe 7.16. 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  |     7 | | Wednesday |     4 | +-----------+-------+ 

Notice that because the query includes no ORDER BY clause, the rows 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 CAST⁠(⁠ ⁠ ⁠) function:

mysql> SELECT day, day+0 FROM weekday ORDER BY CAST(day AS CHAR); +-----------+-------+ | 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 nonenumeration column in a specific nonlexical order, consider changing the data 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 that 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 | +--------+ 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net