Ответ 1

Помните, что IP-адреса не являются текстовым адресом, а цифровым идентификатором. У меня аналогичная ситуация (мы делаем поиск гео-ip), и если вы сохраняете все свои IP-адреса в виде целых чисел (например, мой IP-адрес равен 192.115.22.33, поэтому он хранится как 3228767777), тогда вы можете искать IP-адреса легко используя операторы правого сдвига.

Недостатком всех этих типов поиска является то, что вы не можете использовать индексы, и вам нужно выполнять полное сканирование таблицы всякий раз, когда вы выполняете поиск. Вышеупомянутая схема может быть улучшена путем сохранения как сетевого IP-адреса сети CIDR (начало диапазона), так и широковещательного адреса (конец диапазона), так, например, для хранения 192.168.1.0/24 вы можете хранить два столбцы:

network     broadcast
3232235776, 3232236031 

И тогда вы можете совместить его, просто выполните

SELECT count(*) FROM bans WHERE 3232235876 >= network AND 3232235876 <= broadcast

Это позволит вам сохранять сети CIDR в базе данных и быстро и эффективно сопоставлять их с IP-адресами, используя быстрые числовые индексы.

Примечание из обсуждения ниже:

MySQL 5.0 включает оптимизацию запросов в диапазоне, называемую " слияние индексов, что позволяет ускорить такие запросы (и избежать полного сканирования таблицы) пока:

• Существует индекс нескольких столбцов, который точно соответствует столбцам в запросе. Итак, для приведенного выше примера запроса индекс должен быть (network, broadcast).
• Все данные могут быть получены из индекса. Это верно для COUNT(*), но не соответствует true для SELECT * ... LIMIT 1.

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

Ответ 2

Адреса IPv4, сетевые адреса и сетевые маски - все номера UINT32 и представлены в форме, удобной для чтения, как "пунктирные квадратики". Код таблицы маршрутизации в ядре выполняет очень быстрое битовое сравнение И при проверке, находится ли адрес в заданном сетевом пространстве (сеть/сетевая маска). Трюк здесь заключается в том, чтобы сохранить IP-адреса с точками квадрата, сетевые адреса и сетевые маски в ваших таблицах как UINT32, а затем выполнить те же 32-битные бит-мудрые И для вашего соответствия. например,

SET @test_addr = inet_aton('1.2.3.4');
SET @network_one = inet_aton('1.2.3.0');
SET @network_two = inet_aton('4.5.6.0');
SET @network_netmask = inet_aton('255.255.255.0');

SELECT (@test_addr & @network_netmask) = @network_one AS IS_MATCHED;
+------------+
| IS_MATCHED |
+------------+
|          1 |
+------------+

SELECT (@test_addr & @network_netmask) = @network_two AS IS_NOT_MATCHED;
+----------------+
| IS_NOT_MATCHED |
+----------------+
|              0 |
+----------------+

Ответ 3

Для IPv4 вы можете использовать:

SET @length = 4;

SELECT  INET_NTOA(ipaddr), INET_NTOA(searchaddr), INET_NTOA(mask)
FROM  (
  SELECT
        (1 << (@length * 8)) - 1 & ~((1 << (@length * 8 - cidr)) - 1) AS mask,
        CAST(CONV(SUBSTR(HEX(ipaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS ipaddr,
        CAST(CONV(SUBSTR(HEX(@myaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS searchaddr
  FROM  ip
) ipo
WHERE ipaddr & mask = searchaddr & mask

Ответ 4

Хммм. Вы можете создать таблицу cidr-масок, присоединиться к ней, а затем сравнить ip anded (& в MySQL) с маской с блокировкой ipaddress. Будет ли это делать то, что вы хотите?

Если вы не хотите создавать таблицу масок, вы можете вычислить маску как -1 << (x-cidr) с зависимостями x = 64 или 32.

Ответ 5

Генерация диапазонов IP-адресов в виде целых чисел

Если ваша база данных не поддерживает необычные побитовые операции, вы можете использовать упрощенный целочисленный подход.

В следующем примере используется PostgreSQL:

select (cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 1) as bigint) * (256 * 256 * 256) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 2) as bigint) * (256 * 256      ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 3) as bigint) * (256            ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 4) as bigint)) 
        as network,

       (cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 1) as bigint) * (256 * 256 * 256) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 2) as bigint) * (256 * 256      ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 3) as bigint) * (256            ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 4) as bigint)) + cast(
          pow(256, (32 - cast(split_part('4.0.0.0/8', '/', 2) as bigint)) / 8) - 1 as bigint
        ) as broadcast;

Ответ 6

MySQL: преобразование диапазона IP в CIDR

Я потратил несколько часов на поиск способа использования MySQL для выбора диапазона IP-адресов и вывода формата CIDR, который охватывает диапазон IP, подходящий для поддержки черного списка IP.

Особенности моей среды и требований Я использую OpenWeb Analytics для регистрации трафика на моем веб-сайте и нескольких, которыми я управляю. Я разработал процесс для извлечения различных IP-адресов, зарегистрированных в OWA, а затем объединить элементы географических данных в записи, которые затем сохраняются в пользовательской таблице. Вооруженные этой таблицей данных IP-to-Location предоставляют возможность сообщать о хитах из нежелательных источников или источниках, не относящихся к моему местному бизнесу - Китаю, Японии, Корее, России и т.д. Это привело к увеличению списка IP-адресов адреса; многие из которых попадают в одну и ту же сеть. Чтобы облегчить требования к обслуживанию моего файла .htaccess для сервера, становится выгоднее записывать черные списки IP-адресов в формате CIDR. Это приводит к необходимости иметь возможность генерировать CIDR из уже зарегистрированных IP-адресов.

Подход MySQL Большинство веб-хостов предоставляют доступ к базам данных MySQL. Однако немногие, если таковые имеются, позволяют создавать функции базы данных. Это немного усложнило кодирование.

Пример SQL, доступный в http://blog.watsoninfotech.com/2012/12/mysql-convert-ip-range-to-cidr.html