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 |
+--------+
18.119.162.49