The Best Way to Store ipv4 & ipv6?

[mattyd]

I've been reseraching the best way(s) to store both ipv4 & ipv6 ip addresses.

Simply put, so far there seems to be much debate as to how to do so properly ranging from storing them as varchar or in a binary format.

I'm really not sure at this point.

I suppose I would like to hear from some folks here as to your experience doing so.

Some things I need to consider:

1. At some point, I may be storing possibly millions of ips, so I do need to consider storage size in advance, obviously.

2. I also assume when I capture ips, I would have to store each type in a seperate row, or can one store both types together then sort them at a later time? I'm sorry, I'm not sure if that question makes sense.

Can both types be caputured in the same manner and treated in a similar manner until I have to actually present them in a readable format to someone in the future? (At this point, I'm not concerned how they appear when stored, as long as they can be dealt with later in a human-readable manner.)

I'm also learning about inet_ntop(), inet_pton() from this site and think this may be somehow related to what I'm inquiring about.

Any hint or push in the right direction would be highly appreciated; a link to an apporiate article/thread/site, etc.

Thank you in advance for your time.

[cereal]

You can use varbinary(16) to store IPV4 (4bytes) and IPV6 (16bytes), MySQL has some functions to convert the IP from a string representation to a blob and reverse, see for INET6_ATON() and INET6_NTOA(), which deals with both IP types:

• https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet6-aton

Note, the HEX() function in the documentation example is used only to show the hexadecimal value of the blob. When you want to store the IP just do, something like this:

INSERT INTO `log_table` (`ip_address`, `created_at`) VALUES(INET6_ATON(?), NOW());

Where ? is the IP to save.
When you want to search do:

SELECT INET6_NTOA(`ip_address`) AS `ip` FROM `log_table` WHERE `ip_address` = INET6_ATON(?);

This allows you to create an index on the ip_address column, which means fast access. So it's important, in the WHERE statement, to convert the IP to the binary representation, not reverse:

SELECT INET6_NTOA(`ip_address`) AS `ip` FROM `log_table` WHERE INET6_ATON(`ip_address`) = ?;

This would be very slow because it would convert each row into a string representation.

In the result set you could also return as blob, so:

SELECT `ip_address` FROM `log_table` WHERE `ip_address` = INET6_ATON(?);

and convert in PHP through inet_ntop():

• http://php.net/manual/en/function.inet-ntop.php

But this requires to compile PHP with IPV6 support, otherwise it will fail. I use the same storing method with file paths:

SELECT UNHEX(MD5(TRIM(TRAILING '/' FROM '/path/to/file')));

It's working fine with +2M rows. Right now I would focus much on the storage size issue.

[cereal]

Right now I would focus much on the storage size issue.

Right now I would NOT focus much on the storage size issue.

Pardon :p

[pty]

I'd use PostgreSQL which has native network address data types.

[mattyd]

Thank you cereal and pty for your help and recommendations.

I am going to review your info this evening and try implenting some of it later tonight.

Until recently, I did not know ip's were such a complex topic; I do not mean merely storing them correctly, but the entire scope. I've been reading some other material this evening such as this document. It is a lot to learn but very important. It's fascinating!

I will return here soon with any questions I may have in regards to storing ip's.

Thanks again.

[pty]

That's the best approach. My suggestion makes sense if you don't have a pile of stuff to migrate, because all the hard work has been done. If migration is a big complex task, cereal's approach is very sensible and more portable between databases/stores.