You are viewing a read-only archive of the Blogs.Harvard network. Learn more.
Skip to content

Storing IP Addresses in MySQL with ruby/rails

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 =‘’)
=> #
>> puts ip.to_i
=> 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 =, Socket::AF_INET).to_s
=> “”

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 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 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

You must be logged in to post a comment.