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

When reading the third edition of High Performance MySQL (section 4.1.7), the author advises storing IPv4 addresses as a 32‑bit UNSIGNED INT rather than as a string, but does not give a detailed rationale.

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

It saves storage space for both the data and its indexes.

It facilitates efficient range queries (BETWEEN … AND) with higher performance.

An IPv4 address requires between 7 and 15 characters, so a VARCHAR(15) column is sufficient. However, MySQL adds an extra byte to store the length of a variable‑length string, while an UNSIGNED INT always occupies exactly 4 bytes.

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

Drawbacks of the integer representation are:

Reduced human readability.

Need for manual conversion between the numeric and dotted‑decimal forms.

MySQL provides built‑in functions to handle these conversions:

INET_ATON('192.168.0.1') converts a dotted‑decimal string to an integer.

INET_NTOA(3232235521) converts an integer back to 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 addresses, the same advantages can be achieved with a VARBINARY column and the functions INET6_ATON and INET6_NTOA .

Conversion can also be performed in application code. The following Java class demonstrates how to convert between a dotted‑decimal IPv4 string and a long value:

package com.mikan;

/**
 * @author Mikan
 */
public class IpLongUtils {
    /**
     * Convert a 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 a 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"));
    }
}

Running the program produces:

3232235521
192.168.0.1
167772161

The article concludes with a brief note about a technical community group, which is unrelated to the technical content.