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

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 strings, but does not explain why; this article clarifies the reasons.

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

It saves space in both data rows and indexes.

It enables more efficient range queries (BETWEEN … AND).

An IPv4 address requires 7 to 15 characters, so VARCHAR(15) is sufficient, but MySQL adds an extra byte for length information; an UNSIGNED INT always occupies only 4 bytes.

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

For IPv6, VARBINARY can provide similar benefits, with MySQL offering INET6_ATON and INET6_NTOA conversion functions.

MySQL provides built‑in functions to convert between string and integer forms:

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 class demonstrates converting between dotted‑decimal strings and long values:

package com.mikan;
/**
 * @author Mikan
 * @date 2015-09-22 10:59
 */
public class IpLongUtils {
    /**
     * Convert 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 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

Author: MikanMu (source: blog.csdn.net/mhmyqn/article/details/48653157).