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

When storing IPv4 addresses, the author of "High Performance MySQL 3rd Edition" recommends using a 32‑bit UNSIGNED INT instead of a string, citing space savings and more efficient range queries.

Compared with VARCHAR(15), which needs 1‑2 extra bytes for length metadata, an UNSIGNED INT always occupies only 4 bytes, reducing both data and index storage.

Using separate integer columns for each octet is possible but generally less space‑efficient and slower for queries.

MySQL provides built‑in functions to convert between string and integer representations:

INET_ATON('192.168.0.1') returns the integer 3232235521.

INET_NTOA(3232235521) returns the string '192.168.0.1'.

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 addresses, the same advantages apply using VARBINARY together with INET6_ATON and INET6_NTOA conversion functions.

Although integer storage is not human‑readable and requires conversion, the conversion can be handled in the application layer. Below is a Java utility class that converts between dotted‑decimal strings 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"));
    }
}

3232235521
192.168.0.1
167772161

The article concludes that using UNSIGNED INT for IPv4 (and VARBINARY for IPv6) offers clear storage and performance benefits, while conversion functions and simple utility code mitigate readability drawbacks.