0

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.

Edited by mattyd: content, spelling, punct., linking

3
Contributors
5
Replies
27
Views
4 Weeks
Discussion Span
Last Post by pty
2

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:

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():

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.

Edited by cereal

2

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

1

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.

1

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.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.