поиск php на частичном IP-адресе, хранящемся в mysql как unsigned int
В настоящее время у меня есть MySQL базы данных, которая включает в себя IP-адреса. В форме поиска клиент хочет выполнить поиск по частичному IP-адресу и получить (возможно) много результатов. В настоящее время я храню IP-адреса в mysql как unsigned int. Я использую php 5.2, поэтому у меня нет доступа к php 5.7 и его функции INET6_NTOA.
Текущая база данных насчитывает более 50 000 записей и продолжает расти, поэтому я не хочу преобразовывать все IP-адреса в точечную запись, а затем делать сопоставление — что кажется немного громоздким.
Есть ли лучший способ поиска по частичному IP-адресу? Клиент настаивает на этом, поэтому выбора нет.
PS. Я отображаю данные в datatables-1.10, но это не должно иметь ничего общего с тем, как я ищу — просто к сведению.
Решение
Предполагая, что вы имеете дело с адресами IPv4, каждый адрес является не чем иным, как 32-битным.
Есть MySQL INET_NTOA функция, которая отвечает за возврат строки по вашему IP.
Итак, вы можете использовать что-то вроде:
SELECT ... FROM ... WHERE INET_NTOA(...) LIKE (...)
Надеюсь, поможет.
UPD: для повышения производительности я бы предложил вам обновить таблицу, добавив новые CHAR(16) поле для строкового представления IP и триггера ON UPDATE который должен заполнить это поле INET_NTOA(...) значение. Выбор против этого поля будет работать как шарм.
Другие решения
На самом деле, столбец целых чисел без знака уже является наиболее эффективным способом поиска совпадений по частичным IP-адресам! Пожалуйста, не тратьте свою энергию и время ЦП на преобразование обратно в точечную запись или в поиск типа LIKE в каком-либо строковом столбце.
Существует несколько способов записи частичного IP-адреса, но, в конце концов, все они сводятся к базовому IP-адресу с сетевой маской. Кроме того, предполагая, что под частичным вы подразумеваете все IP-адреса с общим префиксом, тогда это также эквивалентно указанию диапазона IP-адресов.
В любом случае, спецификация частичного IP-адреса в конечном итоге описывается как два 32-битных целых числа без знака, закодированных в том же формате, что и столбец вашей базы данных. Либо у вас есть начальный IP и конечный IP, либо у вас есть базовый IP и маска. Эти целые числа могут использоваться непосредственно внутри вашего запроса SQL для эффективного получения совпадений. Еще лучше, если вы используете подход диапазона ip, то движок сможет воспользоваться упорядоченным индексом в вашем столбце ip. Вы не можете ожидать лучшего.
Так как же построить диапазон IP? Это будет зависеть от того, как ваши частичные адреса были указаны в первую очередь, но, если вы знаете маску сети, тогда начальный адрес равен (base ip & маска сети), и конечный адрес ((базовый IP & сетчатая маска) | (~ маска сети)), где &, | и ~ соответственно означает побитовое, а побитовое — или побитовое — нет.
Обновить
Вот пример кода для применения описанной мной стратегии.
Прошло очень много времени с тех пор, как я последний раз писал PHP-код, и следующее никогда не выполнялось, поэтому прошу прощения за любую ошибку, которую я мог внести. Я также сознательно решил «расширить» каждый сценарий обозначений, чтобы облегчить их понимание, а не сжать их все в одном очень сложном регулярном выражении.
if (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) [/] (\d{1,2}) $/x', $input, $r)) {
// Four-dotted IP with number of significant bits: 123.45.67.89/24
$a = intval($r[1]);
$b = intval($r[2]);
$c = intval($r[3]);
$d = intval($r[4]);
$mask = intval($r[5]);
} elseif (preg_match(' /^ (\d{1,3}) (?: [.] [*0] [.] [*0] [.] [*0] )? $/x', $input, $r)) {
// Four-dotted IP with three-last numbers missing, or equals to 0 or '*':
// 123.45, 123.45.0.0, 123.45.*.* (assume netmask of 8 bits)
$a = intval($r[1]);
$b = 0;
$c = 0;
$d = 0;
$mask = 8;
} elseif (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) (?: [.] [*0] [.] [*0] )? $/x', $input, $r)) {
// Four-dotted IP with two-last numbers missing, or equals to 0 or '*':
// 123.45, 123.45.0.0, 123.45.*.* (assume netmask of 16 bits)
$a = intval($r[1]);
$b = intval($r[2]);
$c = 0;
$d = 0;
$mask = 16;
} elseif (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) (?: [.] [*0] )? $/x', $input, $r)) {
// Four-dotted IP with last number missing, or equals to 0 or *:
// 123.45.67, 123.45.67.0, 123.45.67.* (assume netmask of 24 bits)
$a = intval($r[1]);
$b = intval($r[2]);
$c = intval($r[3]);
$d = 0;
$mask = 24;
} elseif (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) $/x', $input, $r)) {
// Four-dotted IP: 123.45.67.89 (assume netmask of 32 bits)
$a = intval($r[1]);
$b = intval($r[2]);
$c = intval($r[3]);
$d = intval($r[4]);
$mask = 32;
} else {
throw new Exception('...');
}
if ($a < 0 || $a > 255) { throw new Exception('...') };
if ($b < 0 || $b > 255) { throw new Exception('...') };
if ($c < 0 || $c > 255) { throw new Exception('...') };
if ($d < 0 || $d > 255) { throw new Exception('...') };
if ($mask < 1 || $mask > 32) { throw new Exception('...') };
$baseip = ($a << 24) + ($b << 16) + ($c << 8) + ($d);
$netmask = (1 << (32 - $mask)) - 1;
$startip = $baseip & netmask;
$endip = ($baseip & netmask) | (~netmask);
// ...
doSql( "SELECT ... FROM ... WHERE ipaddress >= ? && ipaddress <= ?", $startip, $endip);
// or
doSql( "SELECT ... FROM ... WHERE ((ipaddress & ?) = ?)", $netmask, $startip);
Вот.
$ip = '127.5.3';
if (preg_match('/^([0-9]*)?\.?([0-9]*)?\.?([0-9]*)?\.?([0-9]*)$/',$ip, $m)) {
$from = (int)$m[1]*256*256*256 +(int)$m[2]*256*256 + (int)$m[3]*256 + (int)$m[4];
// or $from = ip2long($m[1].'.'.$m[2].'.'.$m[3].'.'.$m[4]);
$to = ($m[1]>0?$m[1]:255)*256*256*256 + ($m[2]>0?$m[2]:255)*256*256 + ($m[3]>0?$m[3]:255)*256+($m[4]>0?$m[4]:255);
// select * from sometable where ip between $from and $to
} else
echo "Incorrect IP";
Так как вы хотите частичный поиск и вернуть список с соответствующими ips, я бы предложил использовать LIKE, а затем% в конце
SELECT ip FROM ip_table
WHERE ip LIKE '$ip%'