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

When reading High Performance MySQL (3rd edition, section 4.1.7), the author advises storing IPv4 addresses as a 32‑bit UNSIGNED INT instead of a string, but does not detail the reasons.

Using an unsigned integer offers clear advantages over string storage: it reduces both data and index space, enables efficient BETWEEN … AND range queries, and always occupies a fixed 4 bytes.

A typical IPv4 address requires 7‑15 characters, so a VARCHAR(15) column is often used; however MySQL stores variable‑length strings with an extra length byte, resulting in up to 16 bytes per row, whereas an UNSIGNED INT consistently uses only 4 bytes.

Alternative designs, such as storing each octet in separate columns, generally provide poorer storage efficiency and query speed, while IPv6 addresses can be stored similarly using VARBINARY together with INET6_ATON and INET6_NTOA functions.

The main drawbacks of integer storage are reduced human readability and the need for conversion between numeric and dotted‑decimal forms.

MySQL supplies built‑in functions for this purpose: INET_ATON('192.168.0.1') returns the integer 3232235521, and INET_NTOA(3232235521) converts it back to the string representation. Example query:

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)

Conversion can also be performed in application code; the following Java utility class demonstrates converting between dotted‑decimal strings and long values:

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

Running the program produces:

3232235521
192.168.0.1
167772161