Why Store IPv4 Addresses as UNSIGNED INT in MySQL and How to Convert Them

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

Using an UNSIGNED INT offers several advantages over VARCHAR storage:

It saves storage space for both data and indexes.

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

Typically an IPv4 address requires 7 to 15 characters, so a VARCHAR(15) column is sufficient. MySQL stores the length of a variable‑length string in an extra byte, whereas an unsigned integer always occupies exactly 4 bytes.

Storing each octet in separate columns is possible but generally does not improve storage efficiency or query speed.

Disadvantages of the integer representation include reduced readability and the need for manual conversion.

MySQL provides built‑in functions to handle conversion:

<code>SELECT INET_ATON('192.168.0.1');  -- returns 3232235521
SELECT INET_NTOA(3232235521);    -- returns '192.168.0.1'</code>

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

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

<code>package com.mikan;

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(".")
          .append((ipLong >>> 16) & 0xFF).append(".")
          .append((ipLong >>> 8) & 0xFF).append(".")
          .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"));
    }
}</code>

The program outputs:

<code>3232235521
192.168.0.1
167772161</code>