Как производить вычисления ip-адресов силами только sql?

Question

[Сергей]

Есть у нас на работе база Mysql. В ней есть следующие столбцы (описание - название - тип поля):
1. Ip-адрес подсети - IpAddr - INT
2. Маска подсети - Mask - INT
3. Первый узел подсети - StartIp - INT
4. Броадкаст подсети - EndIp - INT
5. Id точки из другой таблицы - IdFilial - INT
Ip-адреса хранятся в виде Integer и туда-сюда конвертятся нативной командой INET_ATON и INET_NTOA.
Если требуется найти, какой сети соответствует ip адрес, то создается запрос, содержащий примерно такое условие

WHERE StartIp < $Ipaddress AND EndIp > $Ipaddress

У меня есть желание сократить таблицу, убрав столбцы StartIp и EndIp. Никак не соображу, какого вида вычисления должны быть для подобного запроса?

Answer 1

[Mercury13]

IpAddress & Mask = IpAddr
(при условии, что IP гарантированно настоящий и не надо узнавать, это настоящий адрес или один из сервисных, связанных с сетью)
Возможно — я не знаю, каких размеров таблица — БД держат в таком виде ради индексирования.

Comments

[Сергей]

У меня есть гарантированно легитимный адрес из диапазона указанной подсети. Эти адреса будут забираться с реальных компьютеров. А вот какой запрос должен быть, чтобы понять, что айпишник из данного диапазона?
К примеру, у нас указана сеть в БД 10.10.10.0/29 и есть айпишник 10.10.10.2. Какое правило для выборки в этом случае получится?

[Mercury13]

IpAddress & Mask = IpAddr

[Mercury13]

Возможно, эффективнее будет запрос

SELECT * FROM (
  SELECT * FROM x
  WHERE IpAddr <= IpAddress
  ORDER BY IpAddr ASC
  LIMIT 1) AS q
WHERE IpAddress & q.Mask = q.IpAddr

Это уже надо смотреть через EXPLAIN.

[Mercury13]

Смысл. Сначала получаем кандидата через индекс, затем проверяем, таков ли он.

[Mercury13]

Ну разумеется, здесь нужен индекс по IpAddr, и если такового нет — эффективнее в лоб.

[Mercury13]

Если маска подсети — это не маска как таковая, а кол-во битов, то

IpAddress & (CONVERT(INT, '0xFFFFFFFF') << q.MaskBits) = q.IpAddr

[Сергей]

Mercury13: >IpAddress & Mask = IpAddr
Не совсем понял, каким образом мне это поможет? У меня есть 3 записи:
10.10.10.0/29
10.10.11.0/29
10.10.12.0/29
и адрес 10.10.10.2, по которому я должен узнать принадлежность к подсети. Для этого адреса у меня нет маски. Просто список адресов, просканенных тем же nmap-ом. На сетку 10.0.0.0 натравил сканер, записал список живых айпишников в файл, распарсил его и каждый должен проверить на вхождение в диапазон. Поэтому вопрос стоит все таки так: как при наличии маски сети и адреса сети определить диапазон адресов и проверить вхождение адреса в диапазон?

[Mercury13]

Если там действительно маска

SELECT * FROM x
WHERE INET_ATON('10.10.10.2') & Mask = IpAddr

Если там кол-во битов — я был не совсем прав…

SELECT * FROM x
WHERE INET_ATON('10.10.10.2') & (CONVERT(INT, '0xFFFFFFFF') << (32 - Mask)) = IpAddr

Это без оптимизации, в самом простом виде.