Why Store IPv4 as Unsigned INT in MySQL? Benefits, Drawbacks & Java Conversion

When reading "High Performance MySQL" (3rd edition, §4.1.7), the author suggests storing IPv4 addresses as a 32‑bit unsigned integer (UNSIGNED INT) rather than as a string, but does not explain why.

Advantages of using an unsigned integer over a string

It saves space for both data and indexes, enables efficient range queries (BETWEEN … AND), and improves performance. A VARCHAR(15) column needs 7–15 characters plus one extra byte for length, whereas an UNSIGNED INT always occupies 4 bytes. Storing each octet in separate columns is usually less efficient.

For detailed performance benchmarks see this article .

Disadvantages of using an unsigned integer

The value is not human‑readable and requires conversion.

MySQL provides functions to convert between the two forms: INET_ATON() to turn a dotted‑decimal string into an integer, and INET_NTOA() to convert back.

mysql> SELECT INET_ATON('192.168.0.1');
+--------------------------+
| INET_ATON('192.168.0.1') |
+--------------------------+
|               3232235521 |
+--------------------------+

mysql> SELECT INET_NTOA(3232235521);
+-----------------------+
| INET_NTOA(3232235521) |
+-----------------------+
| 192.168.0.1          |
+-----------------------+

For IPv6, similar benefits can be achieved with VARBINARY and the functions INET6_ATON() and INET6_NTOA(). Conversions can also be performed in the application layer.

Java utility for converting between string and long

package com.mikan;

public class IpLongUtils {
    /** Convert string IP to long */
    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]);
    }

    /** Convert long IP to string */
    public static String long2Ip(long ipLong) {
        StringBuilder ip = new StringBuilder();
        ip.append(ipLong >>> 24).append(".");
        ip.append((ipLong >>> 16) & 0xFF).append(".");
        ip.append((ipLong >>> 8) & 0xFF).append(".");
        ip.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