Why Store IPv4 Addresses as UNSIGNED INT in MySQL? Benefits, Drawbacks & Java Example

When storing IPv4 addresses in MySQL, the book "High Performance MySQL" recommends using a 32‑bit UNSIGNED INT instead of a string, but does not explain why.

Compared with VARCHAR, an unsigned integer saves storage space (4 bytes vs up to 15 bytes plus a length byte), allows faster range queries (BETWEEN … AND), and reduces index size.

An IPv4 address needs 7‑15 characters, so VARCHAR(15) is sufficient, but each variable‑length string also stores an extra length byte.

The MySQL functions INET_ATON() (string → integer) and INET_NTOA() (integer → string) handle the conversion automatically.

For IPv6, the same benefits can be achieved with VARBINARY and the functions INET6_ATON() and INET6_NTOA().

The main drawback of integer storage is reduced readability and the need for conversion, which can be handled in the application layer. An example Java utility class that converts between dotted‑decimal strings and long values is shown below.

package com.mikan;

public class IpLongUtils {
    public static long ip2Long(String ipStr) {
        String[] ip = ipStr.split("\\.");
        return (Long.valueOf(ip[0]) << 24) + (Long.valueOf(ip[1]) << 16)
                + (Long.valueOf(ip[2]) << 8) + Long.valueOf(ip[3]);
    }

    public static String long2Ip(long ipLong) {
        StringBuilder ip = new StringBuilder();
        ip.append(ipLong >>> 24).append('.')
          .append((ipLong >>> 16) & 0xFF).append('.')
          .append((ipLong >>> 8) & 0xFF).append('.')
          .append(ipLong & 0xFF);
        return ip.toString();
    }

    public static void main(String[] args) {
        System.out.println(ip2Long("192.168.0.1"));
        System.out.println(long2Ip(3232235521L));
        System.out.println(ip2Long("10.0.0.1"));
    }
}

Running the program produces:

3232235521
192.168.0.1
167772161

https://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/