A recent project has me thinking about storing of IP addresses in mysql. The natural tendency is to store it as text. My first attempt stored the address as char(16) with a normal index to help speed searches against it. After some reading about high performance MySQL techniques I was reminded that IP addresses in dotted quad form are the least efficient. Instead of storing as a string of characters I could instead convert the dotted quad into a 32 bit integer.
The magic of converting it is pretty easy to find online however if you are using ruby simply install the IPAddr gem.
>> ip = IPAddr.new(‘255.255.255.255’)
=> #
>> puts ip.to_i
4294967295
=> nil
Reversing the process isn’t quite as easy and the documentation fails to mention this possibility. A little digging online will unearth this additional parameter that is needed:
>> ipnum = 4294967295
=> 4294967295
>> ip = IPAddr.new(ipnum, Socket::AF_INET).to_s
=> “255.255.255.255”
When I first tried to store this in MySQL I ran into another problem. In my haste I created the column ip_num as an int(11). The code I ran didn’t raise an exception and converted all the ip addresses in the database. However when I viewed the results a large number of ip addresses came back as 127.255.255.255. This ip address converts to 2147483647 as an integer.
If this number looks familiar it is because it is exactly half of the value of 255.255.255.255. It is also the limit of a signed integer.
“The signed range is -2147483648 to 2147483647”
Ensure that you create an unsigned int column for ip addresses to hold the max value of 4294967295.
The unsigned range is 0 to 4294967295.
Post a Comment