Hello, I have a geo database that has regular ip address ranges. I would like to convert them to integers to increase performance. Does anyone know how to do that in mysql if the data is already stored in the database? How can I convert the ips to int? Any help appreciated. Thank you!
The problem is that IPv6 is based on hexidecimal and so whilst you can convert them at the moment to integers by stripping the . it wont work in the future
I don't know if you can convert an ip to an integer, but you can convert an ip to a long using the php function ip2long: http://no.php.net/manual/en/function.ip2long.php
I was hoping there was a way to do it with mysql only. So what I ended up doing is running all 2,349,000 records through a loop with php and converting 000.000.000.000 to 000000000 using INET_ATON(). I didn't want to go that route because of the amount of records... I thought it would be really intensive on the server... boy was I wrong This successfully changed all 5 million (startip,endip) in like 3 minutes! I was impressed. So, if you're storing ip ranges for geoip stuff like maxmind, you may want to store the ips using INET_ATON(). Then when you're doing a lookup, you do something like this... function geoip_encode($ip) { list( $w, $x, $y, $z ) = explode('.', trim($ip)); return( (16777216 * $w) + (65536 * $x) + (256 * $y) + $z ); } $ip = geoip_encode($_SERVER['REMOTE_ADDR']); SQL: SELECT * FROM GeoTable WHERE $ip BETWEEN startIpNum AND endIpNum Thanks guys for your input
There are functions to covert both ways, e.g.: SELECT INET_ATON('192.168.0.10') AS ip_int; SELECT INET_NTOA(3232235530) AS ip_str; http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_inet-aton