Storing IPv4 Addresses in MySQL: Choosing the Optimal Data Type

When asked how to store an IP address in MySQL, many instinctively suggest using a char or varchar(15) column because an IPv4 address looks like 192.168.0.1 or 127.0.0.1. However, the length of an IPv4 address varies from 7 characters ( 0.0.0.0) to 15 characters ( 255.255.255.255), making a fixed‑length string inefficient.

The true nature of an IPv4 address is a 32‑bit unsigned integer; the dotted‑decimal notation is only a human‑readable representation. Converting 192.168.0.1 to its numeric form yields 3232235521. Therefore, the most appropriate MySQL type is INT UNSIGNED, which exactly matches the 32‑bit range.

Below is a table of MySQL integer types with their signed and unsigned ranges (use the UNSIGNED keyword to store only non‑negative values):

Type

Signed Range

Unsigned Range

TINYINT (1 byte, 8 bit)

-128 〜 127

0 〜 255

SMALLINT (2 bytes, 16 bit)

-32768 〜 32767

0 〜 65535

MEDIUMINT (3 bytes, 24 bit)

-8388608 〜 8388607

0 〜 16777215

INT (4 bytes, 32 bit)

-2147483648 〜 2147483647

0 〜 4294967295

BIGINT (8 bytes, 64 bit)

-9223372036854775808 〜 9223372036854775807

0 〜 18446744073709551615

Since an unsigned INT can hold the full IPv4 range, it occupies only 4 bytes, whereas VARCHAR(15) needs up to 16 bytes (15 characters plus a length byte). Moreover, integer indexes are far faster than string indexes because numeric comparisons are constant‑time while string comparisons must examine each character.

Storage space: INT UNSIGNED uses 4 bytes; VARCHAR(15) uses up to 16 bytes.

Retrieval speed: Indexes on integer columns are significantly quicker than indexes on variable‑length strings.

MySQL provides built‑in conversion functions INET_ATON() (dotted‑decimal to integer) and INET_NTOA() (integer to dotted‑decimal), which perform fast bit‑wise operations. Using these functions in application code reduces the load on MySQL and keeps the stored data compact.