Storing an IP address with MYSQL

If you find yourself having to store IP addresses in a database (Let’s just talk about IPv4 addresses for now), the best way to do it is to convert the IP address to an integer. After all, an IP address is nothing more than a 256-based integer with 4 places ( Or a binary based integer with 32 places ). Which is to say it’s a 2564 length ( Or 232 ). Doing the math you’ll see there’s 4,294,967,296 possible IPv4 addresses. That’s 4-billion+ with a B. Give or take some loopback and special reserved blocks of IP addresses.

Most languages have a built-in function to convert IP addresses to integers, and more specifically long integers. For instance, MYSQL has the functions INET_ATON() and INET_NOTA() which allow conversion of Integers to IP addresses and vice versa respectively. In PHP The functions are ip2long() and long2ip(), and the long is necessary to note here. The integer is potentially so large that you’ll have to take special precautions with it. Particularly on the MYSQL side. If storing an IP address as an integer, make sure that one of two things is true in your MYSQL database where you’re storing it (And this probably applies to other SQL iterations as well). The field will need to at least be a BIGINT or an UNSIGNED INT. If you use a SIGNED INT value, you’ll only be able to capture about half of the IP addresses you’re looking for. All other IP addresses above 127.255.255.255 will max out and be stored as this value. This is a lesson that is not fun to learn the hard way.

This is of course preferable to storing the IP address as a VARCHAR where it will take up significantly more bytes of space, and if you have to do any numerical operations on it in your code you’ll have to do some extra operations on it. Treating it as a number for storage and operations is probably preferable.

Now if also needing to store IPv6 addresses, you run into a potentially more complicated scenario. In IPv6 2128 bits are now possible. In other words: 340,282,366,920,938,463,463,374,607,431,768,211,456 addresses. I won’t begin to wonder what letter that number starts with. If needing to store both IPv6 and IPv4, a VARBINARY(16) field should be adequate. The BINARY or VARBINARY fields in MYSQL will allow you to store a significantly large binary digit in a small amount of space using characters.

What’s more MYSQL and I’m sure several other languages allows for storing IPv6 addresses numerically with built-in functions INET6_ATON() and INET6_NOTA().