Как сделать подстановочный поиск для IP-адресов, используя INET_ATON в MySQL?

Question

Как сделать подстановочный поиск для IP-адресов, используя INET_ATON в MySQL?

Я нашел этот метод для хранения IP-адресов в базе данных MySQL как целое число, используя INET_ATON: https://stackoverflow.com/a/5133610/4491952

Поскольку адреса IPv4 имеют длину 4 байта, вы можете использовать INT (UNSIGNED) это имеет ровно 4 байта:

`ipv4` INT UNSIGNED

А также INET_ATON а также INET_NTOA конвертировать их:

INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1"));
SELECT INET_NTOA(`ipv4`) FROM `table`;

Для адресов IPv6 вы можете использовать BINARY вместо:

`ipv6` BINARY(16)

И использовать РНР inet_pton а также inet_ntop для конвертации:

'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")'
'SELECT `ipv6` FROM `table`'
$ipv6 = inet_pton($row['ipv6']);

Но как я могу выполнить поиск по шаблону, например, 192.168.%, Используя функцию INET_ATON и PHP ip2long?

1

inet-aton ip2long mysql php

Решение

Другие решения

Один из хитрых трюков, предлагаемых MySQL, — сдвиг битов Вы можете использовать его, чтобы увидеть, содержится ли ip в блоке адреса, записанном в нотации cidr. Вы можете использовать этот метод, рассматривая ваши адреса как блок X.X.X.X / 16 cidr.

set @cidr_block:='10.20.30.40/16';

select inet_ntoa(inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1))<<(32-substring_index(@cidr_block,'/',-1))) as first_ip,
inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1))<<(32-substring_index(@cidr_block,'/',-1))  as first_ip_num,
inet_ntoa((((inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1)))+1)<<(32-substring_index(@cidr_block,'/',-1)))-1) as last_ip,
(((inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1)))+1)<<(32-substring_index(@cidr_block,'/',-1)))-1  as last_ip_num
;
+-----------+--------------+---------------+-------------+
| first_ip  | first_ip_num | last_ip       | last_ip_num |
+-----------+--------------+---------------+-------------+
| 10.20.0.0 |    169082880 | 10.20.255.255 |   169148415 |
+-----------+--------------+---------------+-------------+
1 row in set (0.00 sec)

Ярлык, чтобы увидеть, находится ли ip в блоке адреса — просто просейте и адрес cidr, и ip, чтобы увидеть, совпадают ли они. Конечно, это будет сканирование таблицы, если применять к сохраненным значениям.

select inet_aton('127.0.0.1')>>16 = inet_aton('127.0.10.20')>>16 as `1 = true`;
+----------+
| 1 = true |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

select inet_aton('127.0.0.1')>>16 = inet_aton('127.10.10.20')>>16 as `0 =  false`;
+-----------+
| 0 = false |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

2

Источник

Accepted Answer

Поиск с подстановочными знаками работает со строками и, поскольку он обычно не может извлекать выгоду из индексов, он имеет тенденцию быть чрезвычайно медленным.

Если вы храните IP-адреса в нормализованном представлении, предназначенном для машин (по сравнению с удобочитаемым точечным обозначением), вы можете рассматривать их как числа, использовать множество стандартных операторов и эффективно использовать индексы. Пример:

SELECT *
FROM foo
WHERE dot_notation LIKE '192.168.%';

… можно переписать как:

SELECT *
FROM foo
WHERE as_integer BETWEEN INET_ATON('192.168.0.0') AND INET_ATON('192.168.255.255');

Даже эти INET_ATON() экземпляры для простоты чтения, вы можете просто ввести полученное целое число. Если вы используете PHP, это тривиально, потому что вы можете перенести его на PHP:

$sql = 'SELECT *
FROM foo
WHERE as_integer BETWEEN ? AND ?';
$params = [
// Not sure whether you still need the sprintf('%u') trick in 64-bit PHP
ip2long('192.168.0.0'), ip2long('192.168.255.255')
];

Я не могу проверить это прямо сейчас, но я понимаю, что это должно работать и с IPv6.

3