Member Avatar for 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 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 because: content, spelling, punct., linking
  • 3 Contributors
  • 5 Replies
  • 7K Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by pty

Dani AI

Generated

If you want one table/column that cleanly holds both IPv4 and IPv6 and scales to millions of rows, a practical pattern is to normalize every address to a fixed 16-byte binary representation and index that. For IPv4, either map into the IPv6 space (IPv4-mapped IPv6, per RFC 4291) or zero-extend consistently to 16 bytes. Using a fixed-length type makes indexing and range operations predictable and avoids multiple textual spellings of the same address. When you need to display an address, convert from the 16-byte form to text using a canonical formatter (RFC 5952 recommends a standard text form for IPv6), so you do not end up with duplicates that differ only by formatting.

Example schema:

CREATE TABLE ip_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip BINARY(16) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_ip (ip),
  INDEX ip_idx (ip)
);

Operational tips:

  • Always convert to the 16-byte form in your application or database before insert and in query predicates; avoid wrapping the indexed column in functions so the index remains sargable.
  • If you need subnet membership or block listings, store network boundaries as two 16-byte columns (start_ip, end_ip) and use WHERE ip BETWEEN start_ip AND end_ip. With fixed-length, big-endian network-byte-order values, lexicographic order matches numeric order.
  • For presentation, normalize output per RFC 5952 to keep logs and UIs consistent. For MySQL type behavior and padding details, see BINARY and VARBINARY.

Recommended Answers

All 5 Replies

Member Avatar for 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:

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
Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.