Why Store IPv4 Addresses as UNSIGNED INT in MySQL Instead of VARCHAR

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 rather than as a string, but does not explain why.

Compared with string storage, using an unsigned integer offers several advantages:

It saves both data‑storage space and index‑storage space.

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

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

Other possible designs, such as storing each octet in a separate column, usually do not provide better space or query efficiency.

MySQL provides built‑in functions to convert between the two representations:

INET_ATON('192.168.0.1') returns the integer 3232235521.

INET_NTOA(3232235521) returns the string "192.168.0.1".

Reference: https://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/

Storing IPs as integers also has drawbacks:

Values are less human‑readable.

Manual conversion is required when interacting with applications.

For conversion, MySQL’s INET_ATON and INET_NTOA functions can be used, while IPv6 addresses can be stored as VARBINARY and converted with INET6_ATON and INET6_NTOA .

Below is a Java utility class that performs the same conversions in application code:

package com.mikan;

/**
 * @author Mikan
 */
public class IpLongUtils {
    /**
     * Convert a dotted‑decimal IP string to a long value.
     * @param ipStr IP string
     * @return long representation of the 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 a long value back to a dotted‑decimal IP string.
     * @param ipLong long representation of the IP
     * @return 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

In summary, storing IPv4 addresses as UNSIGNED INT in MySQL reduces storage overhead and improves query performance, while MySQL and custom Java utilities provide straightforward conversion between integer and string forms.