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

When reading High Performance MySQL, 3rd Edition , the author recommends using a 32‑bit UNSIGNED INT to store IPv4 addresses instead of strings, but does not explain why.

Compared with string storage, an unsigned integer offers two main advantages:

It reduces both data and index storage space.

It simplifies and speeds up range queries (e.g., BETWEEN ... AND ).

Typical IPv4 strings require 7–15 characters, so a VARCHAR(15) column is often used. MySQL stores the length of a variable‑length string in an extra byte, whereas an UNSIGNED INT always occupies exactly 4 bytes.

Storing each octet in separate columns is possible but usually less efficient in both space and query performance.

Performance benchmarks confirming these benefits can be found at bafford.com .

Drawbacks of using an integer include reduced human readability and the need for conversion between numeric and dotted‑decimal forms.

MySQL provides built‑in functions for conversion:

INET_ATON('192.168.0.1') returns the integer representation.

INET_NTOA(3232235521) returns the dotted‑decimal 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, using VARBINARY with INET6_ATON and INET6_NTOA provides similar benefits.

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

package com.mikan;

/**
 * @author Mikan
 */
public class IpLongUtils {
    /**
     * Convert dotted‑decimal IP string 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 back to dotted‑decimal IP 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

Overall, using an unsigned integer for IPv4 storage in MySQL improves space efficiency and query performance, while MySQL’s built‑in functions and simple application‑level utilities mitigate the conversion overhead.