Which MySQL datatype to use for an IP address?
Storing an IP address in a database often feels like a trivial task, yet the choice of column type significantly impacts storage efficiency, query performance, and application logic. When you need to capture $_SERVER['REMOTE_ADDR'] or similar server variables, the decision usually boils down to two options: storing the value as a string using VARCHAR(n) or normalizing it into numeric fields like INT or BIGINT. While legacy systems frequently defaulted to VARCHAR(45), modern development practices favor treating IP addresses as structured data rather than arbitrary text.
If you choose VARCHAR, you must decide on a specific length. IPv4 addresses require 15 characters plus the terminating null byte, while IPv6 addresses require 39 characters. Using VARCHAR(45) is a common historical compromise that accommodates both, but it forces your database engine to treat the IP as an opaque string. This approach prevents you from utilizing efficient B-tree indexing based on numeric value and complicates range queries. For instance, finding all IPs in the 192.168.x.x subnet becomes a matter of string pattern matching rather than arithmetic comparison, which can degrade performance on large tables.
The most robust solution for IPv4 addresses is to store them as two TINYINT UNSIGNED columns: one for the first octet (0â255) and another for the second, though storing all four octets in a single UINT or BIGINT is mathematically valid. However, the most practical and scalable approach for mixed IPv4/IPv6 environments without complex custom types is to utilize MySQL's built-in INET_ATON() and INET_NTOA() functions if you are on version 5.6.4 or higher. These functions allow you to store a normalized integer representation of an IPv4 address in a BIGINT UNSIGNED column, or simply use a VARCHAR(45) with strict length constraints if you must support IPv6 without upgrading your schema design for network topology queries.
For pure IPv4 scenarios where range queries are frequentâsuch as logging requests from a specific subnetâa single BIGINT UNSIGNED column is often the best fit. This allows MySQL to index the data numerically, enabling efficient lookups like "find all IPs greater than 192.168.0.1." You can convert the string representation of an IP into this integer using INET_ATON():
CREATE TABLE logs (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
remote_ip BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Inserting data from PHP
-- $ip = $_SERVER['REMOTE_ADDR'];
-- INSERT INTO logs (remote_ip) VALUES (INET_ATON($ip));
Retrieving the readable format later is handled by INET_NTOA(). This normalization ensures that sorting and grouping operations work logically based on network topology rather than lexicographical string order. For example, sorting a list of IP strings alphabetically would group 10.x.x.x before 2.x.x.x, which is incorrect for network analysis; numeric storage prevents this pitfall entirely.
When dealing with IPv6, the situation shifts slightly because the address space exceeds the range of a standard 32-bit integer. In this case, VARCHAR(45) remains the standard recommendation unless you are using MySQL 8.0 or later, which supports an INET6_ATON() function that allows storage in a BIGINT UNSIGNED column (though note that IPv6 addresses technically require more bits than a 64-bit integer can hold if considering the full 128-bit space, so strict BIGINT usage for IPv6 is often discouraged unless truncated or handled via specific JSON/BLOB strategies). Actually, since an IPv6 address is 128 bits and a MySQL BIGINT is 63 bits (plus sign), storing a full IPv6 address as an integer is mathematically impossible without truncation or using a string. Therefore, for IPv6, VARCHAR(45) is technically necessary to preserve the full address integrity unless you are specifically filtering for compressed formats or utilizing external libraries for conversion.
Ultimately, if your application needs to handle both protocols seamlessly and perform complex network operations, sticking to VARCHAR(45) with a constraint ensuring it matches the IP regex pattern provides the safest route without requiring schema migrations every time an IPv6 address appears in the logs. If you are strictly on IPv4 and want optimal performance for subnet filtering, migrating to a normalized integer column via INET_ATON() offers superior query capabilities that string-based columns cannot match.
