Как в mysql лучше всего хранить ip адрес? Varchar или int? По вашему мнению, бывают ли ситуации, когда нужно производить поиск по ip адресам, например ip > 127.0.0.1. Какие функции преобразование ip адреса есть в MySQL, а какие есть в php? Равнозначны ли они?
Наиболее удобно ip-адрес в MySQL хранить в типе INT. Причём обязательно UNSIGNED (беззнаковый), чтобы адреса выше 127. вполне вмещались в эти 4 байта.
1
|
`ip` int (10) unsigned NOT NULL |
Тип INT более удобен для хранения ip-адресов по сравнению с CHAR(15) по двум причинам:
- Требует меньше памяти (4 байта INT против 15 байт CHAR). Как результат, таблица занимает меньше места на диске, а скорость выполнения запросов возрастает, т.к. индекс меньшего размера.
- Удобно производить выборки по диапазонам адресов и маскам, а также делать сортировку (получается обычная сортировка по числам).
Имеется недостаток по сравнению с хранением ip как plaintxt (char или varchar) в mysql:
- Необходимость дополнительного преобразования ip-строки в число и наоборот для удобства восприятия. Для чего используются функции
INET_ATON
иINET_NTOA
(см. дальше). Но это ведь не проблема.
Как видно, достоинства хранения ip в UNSIGNED INT сильно превосходят единственный недостаток.
Функции преобразования ip-адреса в mysql:
- INET_ATON() — для преобразования IP адреса в число,
- INET_NTOA() — для обратного преобразования числа в IP адрес.
Для простоты запоминания нужно знать расшифровку аббревиатуры:
ATON
— Address TO Number (адрес в число),NTOA
— Number TO Address (число в адрес).
Примеры преобразования IP в mysql:
1
2
3
4
5
6
7
|
SELECT INET_NTOA(ip) FROM tablename LIMIT 10; # выведет ip в обычном строковом формате SELECT INET_ATON( '127.0.0.1' ); #2130706433 SELECT INET_ATON( '93.125.99.10' ); #1568498442 SELECT INET_NTOA(2130706433); #127.0.0.1 SELECT INET_NTOA(1568498442); #93.125.99.10 |
Аналогичные функции преобразования ip-адреса в PHP:
- ip2long() — для преобразования IP адреса в число;
- long2ip() — для обратного преобразования числа в IP адрес.
1
2
3
4
|
ip2long ( '127.0.0' ); //false ip2long ( '93.125.99.10' ); //1568498442 ip2long ( '127.0.0.1' ); //2130706433 ip2long ( '192.168.0.1' ); //3232235521 |
1
2
3
|
long2ip( '1568498442' ); //93.125.99.10 long2ip( '2130706433' ); //127.0.0.1 long2ip( '3232235521' ); //192.168.0.1 |
Задача. Нам нужно получить все записи, которые находятся в диапазоне адресов 148.100.0.0 — 158.255.255.255. Если хранить IP в виде строк, то пришлось бы производить поиск с помощью регулярных выражений, а запрос выполнялся бы очень долго.
В случае хранения IP-адреса в типе INT sql-запрос будет таким:
1
|
SELECT .... WHERE ip BETWEEN INET_ATON( '148.100.0.0' ) AND INET_ATON( '158.255.255.255' ) |
В случае хранения IP-адреса в виде строк sql-запрос будет таким:
SELECT …. WHERE INET_ATON(ip) BETWEEN INET_ATON(‘148.100.0.0’) AND INET_ATON(‘158.255.255.255’)