Why Store IPv4 Addresses as UNSIGNED INT in MySQL? Benefits & Code

When reading "High Performance MySQL" (3rd edition, section 4.1.7), the author recommends storing IPv4 addresses as 32‑bit unsigned integers (UNSIGNED INT) instead of strings, but does not explain why. This article records the reasons.

Compared with string storage, using an unsigned integer has the following advantages:

Save space for both data and indexes.

Facilitate range queries (BETWEEN … AND) with higher efficiency.

Typically an IPv4 address needs 7 to 15 characters, so VARCHAR(15) is sufficient. MySQL stores the length of a variable‑length string in an extra byte; an unsigned integer always occupies 4 bytes.

Storing each octet in separate columns is possible but usually not space‑ or query‑efficient.

Disadvantages of using an unsigned integer:

Harder to read.

Requires manual conversion.

MySQL provides functions to convert between string and integer representations: INET_ATON() and INET_NTOA(). Example:

mysql> select inet_aton('192.168.0.1');
+--------------------------+
| inet_aton('192.168.0.1') |
+--------------------------+
|            3232235521   |
+--------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(3232235521);
+-----------------------+
| inet_ntoa(3232235521) |
+-----------------------+
| 192.168.0.1          |
+-----------------------+
1 row in set (0.00 sec)

For IPv6, VARBINARY together with INET6_ATON() and INET6_NTOA() offers similar benefits.

Conversions can also be performed in the application layer; the following Java class demonstrates converting between string IPs and long values:

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"));
    }
}

Output:

3232235521
192.168.0.1
167772161