Как в 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) по двум причинам:

  1. Требует меньше памяти (4 байта INT против 15 байт CHAR). Как результат, таблица занимает меньше места на диске, а скорость выполнения запросов возрастает, т.к. индекс меньшего размера.
  2. Удобно производить выборки по диапазонам адресов и маскам, а также делать сортировку (получается обычная сортировка по числам).

Имеется недостаток по сравнению с хранением ip как plaintxt (char или varchar) в mysql:

  1. Необходимость дополнительного преобразования ip-строки в число и наоборот для удобства восприятия. Для чего используются функции INET_ATON и INET_NTOA (см. дальше). Но это ведь не проблема.

Как видно, достоинства хранения ip в UNSIGNED INT сильно превосходят единственный недостаток.

Функции преобразования ip-адреса в mysql:

  1. INET_ATON() — для преобразования IP адреса в число,
  2. 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:

  1. ip2long() — для преобразования IP адреса в число;
  2. 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

В случае хранения 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’)