Why Store IPv4 Addresses as UNSIGNED INT in MySQL: Benefits, Drawbacks, and Conversion Methods

When reading "High Performance MySQL" (3rd edition), the author recommends storing IPv4 addresses as a 32‑bit UNSIGNED INT instead of a string, but does not explain why.

Using an unsigned integer saves storage space for both the data and indexes, allows more efficient range queries (BETWEEN … AND), and reduces the storage size from up to 15 characters (VARCHAR) plus a length byte to a fixed 4 bytes.

MySQL provides built‑in functions to convert between the textual and numeric forms: INET_ATON converts a dotted‑quad string to an integer, and INET_NTOA converts the integer back to a string.

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, the VARBINARY type together with INET6_ATON and INET6_NTOA offers similar benefits.

The numeric representation also has drawbacks: it is less human‑readable and requires explicit conversion in queries or application code.

In Java, the conversion can be performed manually; the article includes a utility class that transforms an IPv4 string to a long and back, with a sample main method and its output.

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

3232235521
192.168.0.1
167772161

The article also links to a benchmark that compares the performance of integer versus string storage for IP addresses.

After the technical discussion, the author adds promotional material for JetBrains IDE licenses, activation codes, and related resources.