Storing IPv4 as Unsigned Int in MySQL: Benefits, Drawbacks & Code

When designing a schema for IPv4 addresses, the high‑performance MySQL book recommends using a 32‑bit unsigned integer (UNSIGNED INT) instead of a string. This article investigates the reasons behind that recommendation.

Why an unsigned integer is better

It reduces storage space for both the data rows and the indexes.

It enables efficient range queries with BETWEEN ... AND, which are faster than string comparisons.

IPv4 addresses need between 7 and 15 characters as text, so a VARCHAR(15) column is usually chosen. MySQL stores an extra length byte for variable‑length strings, while an unsigned integer always occupies exactly 4 bytes.

Storing each octet in separate columns is possible but generally offers no space or performance advantage.

Drawbacks of the integer representation

Human readability is lost; the numeric value does not look like an IP address.

Applications must convert between the numeric and dotted‑decimal forms.

MySQL conversion functions

MySQL provides built‑in functions to handle the conversion: INET_ATON('192.168.0.1') returns 3232235521. INET_NTOA(3232235521) returns '192.168.0.1'.

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)

IPv6 support

For IPv6, use VARBINARY to store the 16‑byte address and the functions INET6_ATON / INET6_NTOA for conversion.

Java utility for conversion

The following Java class demonstrates how to convert between the dotted‑decimal string and a long representation, mirroring MySQL's behavior.

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('.')
          .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"));   // 3232235521
        System.out.println(long2Ip(3232235521L));      // 192.168.0.1
        System.out.println(ip2Long("10.0.0.1"));      // 167772161
    }
}

Running the program produces:

3232235521
192.168.0.1
167772161

Further reading

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

In summary, using an unsigned integer for IPv4 storage in MySQL offers clear space savings and query performance benefits, at the cost of readability and the need for conversion logic, which is readily available via MySQL functions or custom code.