What's the Best Way to Store an IP Address in MySQL?

To optimize the storage of an IP address (v4 or v6) in a MySQL database, you should consider using VARBINARY data type for the storage column. This is an optimal way of storing an IP address in a MySQL database because it stores it as byte strings rather than character strings, thus using less storage space.

For example, you can create a table with VARBINARY column for IP address storage in the following way:

CREATE TABLE `event_log` (
    `ip` VARBINARY(16) NOT NULL,
    `comment` VARCHAR(150) NOT NULL
);

VARBINARY(16) is enough to accommodate both, IPv4 and IPv6 addresses. However, if you wish to only store IPv4 addresses, then VARBINARY(4) should be sufficient.

To perform an INSERT, you can convert the string IP address to its corresponding binary representation (that is database storage-ready) by using MySQL INET6_ATON() function, for example, like so:

-- MySQL 5.6.3+
INSERT INTO `event_log` (`ip`, `comment`)
VALUES
    (INET6_ATON('127.0.0.1'), 'ip v4'),
    (INET6_ATON('2001:db8:3333:4444:CCCC:DDDD:EEEE:FFFF'), 'ip v6')
;

As you can see in the example above, the INET6_ATON() function can be used for storing both, IPv4 and IPv6. It returns the following:

• A binary string representation of the IP address for valid arguments;
• NULL for invalid arguments.

Binary strings returned from INET6_ATON() have a max length of 16-bytes for IPv6 addresses and 4-bytes for IPv4 addresses.

To convert the binary strings back to human-readable IP addresses, you can use the MySQL INET6_NTOA() function.