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

Сортировка имен хостов по доменам

Задача
Вы хотите упорядочить имена хостов по доменам, при этом правая часть имени является более важной, чем левая.

Решение
Разбейте имена на части и сортируйте части справа налево.

Обсуждение
Имена хостов – это символьные строки, поэтому их естественным порядком является лексический. Однако часто требуется упорядочить имена хостов по доменам, при этом самые правые сегменты значений являются более значимыми, чем самые левые. Предположим, что у вас есть таблица hostname с именами хостов:

mysql> SELECT name FROM hostname ORDER BY name;

+-----------------------+
| name                       |
+-----------------------+
| cvs.php.net             |
| dbi.perl.org             |
| jakarta.apache.org |
| lists.mysql.com        |
| mysql.com               |
| www.kitebird.com   |
+-----------------------+

Формирование вывода, отсортированного по домену, – это задача сортировки по подстроке. Необходимо извлечь все сегменты имен, чтобы отсортировать их справа налево. Появляется и дополнительная сложность: значения могут содержать разное количество сегментов, как имена хостов из примера (большинство имен состоит из трех частей, но mysql.com – только из двух).

Чтобы извлечь части имен хостов, для начала используем функцию SUBSTRING_INDEX() так же, как и в рецепте 6.13. Значения имен хостов состоят максимум из трех сегментов, так что слева направо можно извлечь их следующим образом:

SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1)

SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1)

SUBSTRING_INDEX(name,'.',-1)

Эти выражения будут работать правильно, если все имена хостов будут состоять ровно из трех частей. Но если имя содержит меньше трех компонентов, корректный результат не получается:

mysql> SELECT name,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1) AS leftmost,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1) AS middle,

-> SUBSTRING_INDEX(name,'.',-1) AS rightmost

-> FROM hostname;

+-----------------------+-----------+---------+------------+
| name                       | leftmost | middle   | rightmost |
+-----------------------+----------+----------+------------+
| cvs.php.net            | cvs         | php       | net           |
| dbi.perl.org             | dbi         | perl       | org           |
| lists.mysql.com        | lists        | mysql    | com          |
| mysql.com               | mysql      | mysql   | com          |
| jakarta.apache.org | jakarta   | apache | org           |
| www.kitebird.com   | www       | kitebird | com          |
+-----------------------+-----------+----------+-----------+

Обратите внимание на вывод для строки mysql.com; в столбце leftmost присутствует значение mysql, хотя там должна бы быть пустая строка. Выраженияизвлечения сегментов действуют так: они отбрасывают n сегментов справа и возвращают самый левый сегмент результата. Источник проблемы с mysql.com в том, что если сегментов меньше, чем n, то выражение просто возвращает самый левый сегмент. Чтобы исправить ошибку, добавим в начало имени хоста достаточное количество точек, чтобы гарантировать наличие необходимого количества сегментов:

mysql> SELECT name,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1)

-> AS leftmost,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1)

-> AS middle,

-> SUBSTRING_INDEX(name,'.',-1) AS rightmost

-> FROM hostname;

+-----------------------+-----------+----------+-----------+
| name                        | leftmost | middle   | rightmost |
+-----------------------+-----------+----------+-----------+
| cvs.php.net             | cvs         | php       | net           |
| dbi.perl.org              | dbi          | perl      | org           |
| lists.mysql.com         | lists        | mysq     | com         |
| mysql.com                |               | mysql     | com         |
| jakarta.apache.org | jakarta    | apache | org           |
| www.kitebird.com    | www      | kitebird  | com          |
+------------------------+----------+----------+-------------+

Выражения выглядят отвратительно, но обеспечивают извлечение подстрок, необходимых для корректной сортировки значений имен хостов справа налево:

mysql> SELECT name FROM hostname

-> ORDER BY

-> SUBSTRING_INDEX(name,'.',-1),

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1),

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1);

+-----------------------+
| name                       |
+-----------------------+
| www.kitebird.com   |
| mysql.com               |
| lists.mysql.com        |
| cvs.php.net             |
| jakarta.apache.org |
| dbi.perl.org             |
+-----------------------+

Если максимальное количество сегментов имени хоста равно не трем, а четырем, то необходимо добавить в инструкцию ORDER BY еще одно выражение SUBSTRING_INDEX(), которое помещает в начало значения три точки.

Статьи по MySQL на эту тему:

Размещение некоторых значений в начале или конце упорядоченного списка
Сортировка IP-адресов в числовом порядке
Сортировка в порядке, определенном пользователем
Сортировка значений ENUM