Выберите CIDR, который находится в диапазоне IP

Хранение IP-адресов в точечно-квадратной нотации в виде VARCHAR — не самый оптимальный способ их хранения, поскольку точечная четверка — это удобное для человека представление 32-битного целого числа без знака, которое не поддается индексированию в базе данных. Но иногда это принципиально удобнее, и в небольших масштабах тот факт, что запросы требуют сканирования таблицы, обычно не является проблемой.

Хранимые функции MySQL — это хороший способ инкапсулировать относительно сложную логику в простой функции, на которую можно ссылаться в запросе, что может привести к более простым для понимания запросам и уменьшению количества ошибок копирования/вставки.

Итак, вот хранимая функция, которую я написал под названием find_ip4_in_cidr4(). Она работает аналогично встроенной функции FIND_IN_SET() -- вы даете ему значение и даете ему "набор" (спецификация CIDR), и он возвращает значение, указывающее, находится ли значение в наборе.

Во-первых, иллюстрация функции в действии:

Если адрес находится внутри блока, вернуть длину префикса. Зачем возвращать длину префикса? Ненулевые целые числа являются «истинными», поэтому мы можем просто вернуть 1, но если вы хотите отсортировать совпадающие результаты, чтобы найти самый короткий или самый длинный из нескольких совпадающих префиксов, вы можете ORDER BY вернуть значение функции.

mysql> SELECT find_ip4_in_cidr4('203.0.113.123','203.0.113.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('203.0.113.123','203.0.113.0/24') |
+-----------------------------------------------------+
|                                                  24 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','192.168.0.0/16');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','192.168.0.0/16') |
+-----------------------------------------------------+
|                                                  16 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Не в блоке? Это возвращает 0 (ложь).

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','203.0.113.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','203.0.113.0/24') |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','192.168.0.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','192.168.0.0/24') |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Для адреса со всеми нулями есть особый случай, мы возвращаем -1 (по-прежнему «истина», но сохраняет порядок сортировки):

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','0.0.0.0/0');
+------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','0.0.0.0/0') |
+------------------------------------------------+
|                                             -1 |
+------------------------------------------------+
1 row in set (0.00 sec)

Бессмысленные аргументы возвращают null:

mysql> SELECT find_ip4_in_cidr4('234.467.891.0','192.168.0.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('234.467.891.0','192.168.0.0/24') |
+-----------------------------------------------------+
|                                                NULL |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Теперь кодез:

DELIMITER $$

DROP FUNCTION IF EXISTS `find_ip4_in_cidr4` $$
CREATE DEFINER=`mezzell`@`%` FUNCTION `find_ip4_in_cidr4`(
  _address VARCHAR(15), 
  _block VARCHAR(18)
) RETURNS TINYINT
DETERMINISTIC /* for a given input, this function always returns the same output */
CONTAINS SQL /* the function does not read from or write to tables */
BEGIN

-- given an IPv4 address and a cidr spec,
-- return -1 for a valid address inside 0.0.0.0/0
-- return prefix length if the address is within the block,
-- return 0 if the address is outside the block,
-- otherwise return null

DECLARE _ip_aton INT UNSIGNED DEFAULT INET_ATON(_address);
DECLARE _cidr_aton INT UNSIGNED DEFAULT INET_ATON(SUBSTRING_INDEX(_block,'/',1));
DECLARE _prefix TINYINT UNSIGNED DEFAULT SUBSTRING_INDEX(_block,'/',-1);
DECLARE _bitmask INT UNSIGNED DEFAULT (0xFFFFFFFF << (32 - _prefix)) & 0xFFFFFFFF;

RETURN CASE /* the first match, not "best" match is used in a CASE expression */
  WHEN _ip_aton IS NULL OR _cidr_aton IS NULL OR /* sanity checks */
       _prefix  IS NULL OR _bitmask IS NULL OR
       _prefix NOT BETWEEN 0 AND 32 OR
       (_prefix = 0 AND _cidr_aton != 0) THEN NULL
  WHEN _cidr_aton = 0 AND _bitmask = 0 THEN -1
  WHEN _ip_aton & _bitmask = _cidr_aton & _bitmask THEN _prefix /* here's the only actual test needed */
  ELSE 0 END;

END $$
DELIMITER ;

Проблема, которая не относится к хранимым функциям, а относится к большинству функций на большинстве платформ СУРБД, заключается в том, что когда столбец используется в качестве аргумента функции в WHERE, сервер не может «смотреть назад» через функцию для использования. индекс для оптимизации запроса.

С помощью этого вопроса: запрос MySQL для преобразования CIDR в диапазон IP< /а>

SELECT
    `cidr`
FROM
    cidr_list
WHERE
    INET_ATON('IP') BETWEEN(
        INET_ATON(SUBSTRING_INDEX(`cidr`, '/', 1)) & 0xffffffff ^(
            (
                0x1 <<(
                    32 - SUBSTRING_INDEX(`cidr`, '/', -1)
                )
            ) -1
        )
    ) AND(
        INET_ATON(SUBSTRING_INDEX(`cidr`, '/', 1)) |(
            (
                0x100000000 >> SUBSTRING_INDEX(`cidr`, '/', -1)
            ) -1
        )
    )