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. <\/p>\n
The magic of converting it is pretty easy to find online however if you are using ruby simply install the IPAddr gem. <\/p>\n
\n>> ip = IPAddr.new(‘255.255.255.255’)
\n=> #
\n>> puts ip.to_i
\n4294967295
\n=> nil\n<\/p><\/blockquote>\nReversing 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:<\/p>\n
\n>> ipnum = 4294967295
\n=> 4294967295
\n>> ip = IPAddr.new(ipnum, Socket::AF_INET).to_s
\n=> “255.255.255.255”\n<\/p><\/blockquote>\nWhen 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. <\/p>\n