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.
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.