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

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

Benefits compared to string storage: It reduces both data and index storage space, enables efficient range queries (BETWEEN…AND), and improves performance. A VARCHAR(15) field requires 1‑15 characters plus an extra byte for length, while an UNSIGNED INT always occupies only 4 bytes. Storing each octet in separate columns is generally less efficient.

For detailed performance analysis and benchmarks, see the linked article: MySQL Performance Benefits of Storing Integer IP Addresses .

Drawbacks of using UNSIGNED INT: The stored value is not human‑readable and requires conversion for display.

MySQL provides conversion functions: INET_ATON('192.168.0.1') returns 3232235521 , and INET_NTOA(3232235521) returns '192.168.0.1' . Example output:

+--------------------------+
| inet_aton('192.168.0.1') |
+--------------------------+
|               3232235521 |
+--------------------------+
1 row in set (0.00 sec)
+-----------------------+
| inet_ntoa(3232235521) |
+-----------------------+
| 192.168.0.1          |
+-----------------------+
1 row in set (0.00 sec)

For IPv6, similar benefits can be achieved using VARBINARY with the functions INET6_ATON and INET6_NTOA .

Below is Java code that converts between string IPs and long values:

package com.mikan;

/**
 * @author Mikan
 */
public class IpLongUtils {
    /**
     * Convert string IP to long
     * @param ipStr string IP
     * @return long value of IP
     */
    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
     * @param ipLong long value of IP
     * @return string representation
     */
    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 author invites readers to discuss the topic and share opinions.