Сортировка 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 |
+---------------------+-----------+

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


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



Оцените статью: (0 голосов)
0 5 0

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