Storing IP Addresses In Mysql

One of the more common pieces of data that I store to databases is IP addresses.

IP’s are something you need store for the security and traceability of certain things to certain people. They are simply a nice sequence of 8 bit integers written out in base 10 form with this funny dot notation. For example, your ip address may look like this: 192.168.0.1. I could go on in massive length as to what these things are, how they are used, etc… But that would defeat the purpose of this post, which is to explain how to store the bloody things in Mysql.

For the longest time I knew there had to be a better way to store them than as a Char(15) or as a VarChar(15) (which is the equivalent of saying, I want to store the decimal text version of the number, and which would take up 15 bytes of data everytime i went to store an IP). I knew this from my networking class back at the U of M… For example, I knew you could represent an IP address as a 32 bit unsigned int. It’s the reason that you can pull up a webpage by going to the integer version of the ip address (like this: http://1089054563/).

What I didn’t know until tonight (when I finally took the time to go look it up) is that Mysql has a way to convert IP addresses (in their dotted notation) to integers (the way that is easy to store). It’s two functions basically:

• INET_ATON(‘192.168.1.100’) will store it to an int
• INET_NTOA(3232235530) will undo the store

Pretty simple… For me, this is almost as big as the time I discover how to properly store dates in a database… Yay for saving 11 bytes of data!