MySQL / 6. Сортировка результатов запроса

Сортировка значений ENUM

Задача
Значения ENUM не сортируются так, как другие строковые столбцы.

Решение
Поймите, как они работают, и используйте их возможности себе во благо.

Обсуждение
ENUM считается строковым типом, но значения ENUM обладают особым свойством: они хранятся в числовом формате, причем числовые значения отсортированы именно в том порядке, в каком они перечислены в определении таблицы. Эти числовые значения определяют порядок сортировки вывода перечислимых типов, что может быть полезным. Предположим, что у вас есть таблица weekday, содержащая перечислимый столбец day, который включает в себя названия дней недели:

CREATE TABLE weekday
(day ENUM('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
);

Внутренний формат хранения значений таков: MySQL сопоставляет перечисленным значениям от Sunday до Saturday числовые значения от 1 до 7. Чтобы убедиться в этом, создайте таблицу, используя только что рассмотренное определение, и вставьте в нее запись для каждого дня недели. Чтобы увидеть эффект сортировки, сделаем порядок ввода отличным от порядка значений в списке, вводя дни произвольным образом:

mysql> INSERT INTO weekday (day) VALUES('Monday'),('Friday'),
-> ('Tuesday'), ('Sunday'), ('Thursday'), ('Saturday'), ('Wednesday');

Затем выведем значения и как строки, и как внутренние числовые значения (чтобы получить последние, добавьте к строкам 0, инициируя тем самым преобразование строки в число):

mysql> SELECT day, day+0 FROM weekday;

+--------------+---------+
| day              | day+0 |
+--------------+---------+
| Monday       | 2          |
| Friday          | 6          |
| Tuesday       | 3          |
| Sunday        | 1          |
| Thursday     | 5          |
| Saturday     | 7           |
| Wednesday | 4           |
+--------------+----------+

Поскольку запрос не содержит инструкцию ORDER BY, записи выводятся неупорядоченными. Если добавить инструкцию ORDER BY day, будет очевидно, что MySQL выполняет сортировку по внутренним числовым значениям:

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

Что тогда делать, если требуется вывести значения ENUM в лексическом порядке? Используйте функцию CONCAT(), чтобы заставить MySQL воспринимать их как строки. Обычно CONCAT() принимает несколько аргументов и объединяет их в одну строку. Но функцию можно использовать и с одним аргументом, что удобно в тех случаях, когда единственное, что вам нужно от CONCAT(), это ее способность выводить строковый результат:

mysql> SELECT day, day+0 FROM weekday ORDER BY CONCAT(day);

+--------------+--------+
| day             | day+0 |
+--------------+--------+
| Friday          | 6         |
| Monday       | 2          |
| Saturday     | 7          |
| Sunday        | 1          |
| Thursday     | 5          |
| Tuesday       | 3          |
| Wednesday | 4          |
+--------------+---------+

Если вы всегда (или почти всегда) сортируете неперечислимый столбец в определенном нелексическом порядке, подумайте о том, чтобы изменить его тип на ENUM, перечислив его значения в соответствующем порядке. Создадим таблицу color, содержащую строковый столбец, и заполним ее тестовыми данными:

mysql> CREATE TABLE color (name CHAR(10));
mysql> INSERT INTO color (name) VALUES ('blue'),('green'),
-> ('indigo'),('orange'),('red'),('violet'),('yellow');

Если сейчас выполнить сортировку по столбцу name, ее порядок будет лексическим, так как столбец содержит значения типа CHAR:

mysql> SELECT name FROM color ORDER BY name;

+---------+
| name    |
+---------+
| blue      |
| green   |
| indigo   |
| orange |
| red       |
| violet    |
| yellow   |
+---------+

Теперь предположим, что вы хотите упорядочить столбец по цветам в том порядке, в котором они расположены в радуге. (Этот порядок задается именем «Roy G. Biv», последовательность букв которого совпадает с первыми буквами цветов радуги.) В качестве одного из способов можно предложить использовать функцию 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    |
+---------+

Чтобы сделать то же самое, не прибегая к помощи FIELD(), используйте предложение ALTER TABLE для преобразования столбца name в тип ENUM, в котором цвета перечислены в нужном порядке:

mysql> ALTER TABLE color
-> MODIFY name
-> ENUM('red','orange','yellow','green','blue','indigo','violet');

После преобразования таблицы сортировка по столбцу name приводит к «радужному» упорядочиванию без каких бы то ни было дополнительных действий:

mysql> SELECT name FROM color ORDER BY name;

+--------+
| name   |
+--------+
| red       |
| orange |
| yellow  |
| green   |
| blue      |
| indigo   |
| violet    |
+--------+