В настоящее время у меня есть mysql db, который включает IP-адреса. В форме поиска клиент хочет выполнить поиск на частичном IP-адресе и иметь (возможно) много результатов. В настоящее время я сохраняю IP-адреса в mysql как unsigned int. Я использую php 5.2, поэтому не имею доступа к php 5.7 и его функции INET6_NTOA.
Текущий db имеет более 50 000 записей и продолжает расти, поэтому я не хочу преобразовывать все IP-адреса в пунктирные нотации, а затем делать совпадение – это кажется немного громоздким.
Есть ли лучший способ для поиска на частичном IP-адресе? Клиент настаивает на этом, поэтому выбора нет.
PS. Я показываю данные в datatables-1.10, но это не должно иметь ничего общего с тем, как я ищу – просто fyi.
Предполагая, что вы имеете дело с адресами IPv4, каждый адрес имеет значение не более 32 бит.
Существует функция MySQL INET_NTOA которая отвечает за возврат строки вашим IP-адресом.
Таким образом, вы можете использовать smth как:
SELECT ... FROM ... WHERE INET_NTOA(...) LIKE (...)
Надеюсь, поможет.
UPD: для повышения производительности я предлагаю вам обновить таблицу, добавив новое поле CHAR(16) для строкового представления IP и триггера ON UPDATE который должен заполнить это поле значением INET_NTOA(...) . Выбор в этом поле будет работать как шарм.
Фактически, целочисленный столбец без знака – это самый эффективный способ поиска совпадений на частичных IP-адресах! Пожалуйста, не тратьте свою энергию или время процессора на преобразование обратно в пунктирную нотацию или для поиска LIKE на каком-то строковом столбце.
Существует несколько способов записи частичного IP-адреса, но в конце концов все они сводятся к базовому ip с сетевой маской. Кроме того, предполагая, что частичным, вы имеете в виду все IP-адреса с общим префиксом, тогда это также эквивалентно заданию диапазона IP-адресов.
В любом случае, спецификация частичного IP-адреса заканчивается тем, что описывается как два 32 бита без знака, закодированные в том же формате, что и столбец базы данных. Либо у вас есть начальный ip и end ip, либо у вас есть базовый ip и маска. Эти целые числа могут использоваться непосредственно в вашем SQL-запросе для эффективного получения совпадений. Еще лучше, если вы используете подход ip range, тогда двигатель сможет использовать упорядоченный индекс в вашем столбце ip. Вы не можете ожидать лучшего.
Итак, как построить IP-диапазон? Это зависит от того, как были указаны ваши частичные адреса в первую очередь, но при условии, что вы знаете маску сети, тогда начальный адрес равен (base ip & net mask), а конечный адрес – ((base ip & net mask) | (~ netmask)), где &, | и ~ соответственно означает поразрядное, а побитовое или побитовое – нет.
Обновить
Вот пример кода для применения описанной вами стратегии.
Теперь, очень давно, когда я в последний раз пишу 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%'