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

Сортировка IP-адресов в числовом порядке

Задача
Вы хотите упорядочить строки, представляющие IP-адреса (четверки чисел, разделенные точками) как числа.

Решение
Разбейте строки на составляющие и сортируйте их как числа. Или просто используйте функцию INET_ATON().

Обсуждение
Если таблица содержит IP-адреса, представленные строками в виде четверок чисел, разделенных точками (например, 111.122.133.144), они будут упорядочиваться лексически. Чтобы сформировать вывод, отсортированный в числовом порядке, можно упорядочивать адреса как значения, состоящие из четырех частей, каждая из которых сортируется как число. Применим прием, наподобие используемого для сортировки имен хостов, но имеющий
следующий отличия:

• Четверки чисел, разделенные точками, всегда состоят из четырех сегментов, так что не нужно добавлять точки в начало значения при извлечении подстрок.

• Значения сортируются слева направо, поэтому порядок, в котором подстроки указываются в инструкции ORDER BY, противоположен использованному для сортировки имен хостов.

• Сегменты значений являются числами, поэтому необходимо добавлять к каждой подстроке ноль, чтобы указать MySQL на необходимость выполнения числовой, а не лексической сортировки.

Предположим, что у вас есть таблица hostip, содержащая строковый столбец ip с IP-адресами:

mysql> SELECT ip FROM hostip ORDER BY ip;

+---------------------+
| ip                          |
+---------------------+
| 127.0.0.1             |
| 192.168.0.10       |
| 192.168.0.2         |
| 192.168.1.10       |
| 192.168.1.2         |
| 21.0.0.1               |
| 255.255.255.255 |
+---------------------+

Результат этого запроса отсортирован в лексическом порядке. Чтобы изменить порядок на числовой, можно извлечь каждый сегмент, добавить ноль для преобразования его в число и поместить в инструкцию ORDER BY

:mysql> SELECT ip FROM hostip
-> ORDER BY
-> SUBSTRING_INDEX(ip,'.',1)+0,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-3),'.',1)+0,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1)+0,
-> SUBSTRING_INDEX(ip,'.',-1)+0;

+---------------------+
| ip                          |
+---------------------+
| 21.0.0.1               |
| 127.0.0.1             |
| 192.168.0.2         |
| 192.168.0.10       |
| 192.168.1.2         |
| 192.168.1.10       |
| 255.255.255.255 |
+---------------------+

Если вы работаете с MySQL версии 3.23.15 или выше, то можете решить задачу проще. Отсортируйте IP-адреса с помощью функции INET_ATON(), которая преобразует сетевой адрес непосредственно в соответствующее число:

mysql> SELECT ip FROM hostip ORDER BY INET_ATON(ip);

+---------------------+
| ip                          |
+---------------------+
| 21.0.0.1               |
| 127.0.0.1             |
| 192.168.0.2         |
| 192.168.0.10       |
| 192.168.1.2         |
| 192.168.1.10       |
| 255.255.255.255 |
+---------------------+

Если вы надеетесь выполнить сортировку, просто добавив ноль к значению ip и применив ORDER BY к результату, посмотрите, какие значения на самом деле будут получены при преобразовании строк в числа:

mysql> SELECT ip, ip+0 FROM hostip;

+---------------------+-----------+
| ip                          | ip+0       |
+---------------------+-----------+
| 127.0.0.1             | 127        |
| 192.168.0.2         | 192.168 |
| 192.168.0.10       | 192.168 |
| 192.168.1.2         | 192.168 |
| 192.168.1.10       | 192.168 |
| 255.255.255.255 | 255.255 |
| 21.0.0.1               | 21          |
+---------------------+-----------+

Преобразование оставляет только ту часть каждого значения, которую можно интерпретировать как число. Оставшаяся часть будет недоступна для сортировки, а для вывода корректного результата нужны все составляющие.

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

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