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;
NULLfor invalid arguments.
Binary strings returned from
INET6_ATON() have a max length of 16-bytes for IPv6 addresses and 4-bytes for IPv4 addresses.
Hope you found this post useful. It was published (and was last revised ). Please show your love and support by sharing this post.