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:

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.


This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.