MySQL IPv4 Storage: Unsigned INT vs String – Benefits, Drawbacks & Java Example

Why use UNSIGNED INT for IPv4 in MySQL

When storing IPv4 addresses, the author of High Performance MySQL, 3rd edition recommends using a 32‑bit UNSIGNED INT rather than a character string. The recommendation is based on storage efficiency and query performance.

Advantages over VARCHAR

Space saving: an unsigned integer occupies exactly 4 bytes, while a VARCHAR(15) needs up to 15 bytes plus an extra length byte.

Index size is smaller, which speeds up look‑ups.

Range queries (e.g., BETWEEN … AND …) are simpler and faster on numeric values.

Disadvantages

Human readability is reduced; the numeric form is not immediately recognizable as an IP address.

Application code must perform conversion between the numeric and dotted‑decimal forms.

MySQL conversion functions

mysql> SELECT INET_ATON('192.168.0.1');
+--------------------------+
| INET_ATON('192.168.0.1') |
+--------------------------+
|               3232235521 |
+--------------------------+

mysql> SELECT INET_NTOA(3232235521);
+-----------------------+
| INET_NTOA(3232235521) |
+-----------------------+
| 192.168.0.1           |
+-----------------------+

IPv6 note

For IPv6 addresses the same principle applies using VARBINARY storage, with MySQL functions INET6_ATON and INET6_NTOA for conversion.

Java utility for conversion

package com.mikan;

public class IpLongUtils {
    /** Convert dotted‑decimal IP string 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 back to dotted‑decimal 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"));   // 3232235521
        System.out.println(long2Ip(3232235521L));      // 192.168.0.1
        System.out.println(ip2Long("10.0.0.1"));      // 167772161
    }
}

Sample output

Running the Java program prints:

3232235521
192.168.0.1
167772161

The numeric representation saves storage space and enables efficient range queries, while the provided conversion utilities address the readability drawback.