Saving / getting IPv4 and IPv6 addresses in MySQL

Question

Sorry, I don't know much about this topic, but all I'm looking for is a quick and easy solution to uniquely represent any IP address (v4 / v6) in MySQL, so I can easily get the last time (if any), that a certain computer visited my site.

I don't need to do any calculations by addresses, just retrieve them, so any unique representation should be fine. I plan to store a lot of them (I don't have an estimate yet), so space can be a problem.

I have seen many solutions for storing IP addresses, but it is not clear what work for both versions. MySQL's built-in INET_ATON does not seem to support IPv6. PHP inet_pton seems promising, but requires prior knowledge of the address format. I'm also not sure about its use (MySQL field type and writing insert statement via PHP). I saw varchar (39) used to represent IPv6 addresses as strings, and I like that this solution is somewhat independent of the server configuration; however, I am a bit concerned about disk space. Would such an approach suffice for all the addresses that $ _SERVER ['HTTP_CLIENT_IP'] could print?

I am a little surprised that there is no obvious general solution. I suggested that this is a very common task. I have hesitancy about this one problem, and I would like to move on to my project. Is a quick and easy solution unreasonable?

Thanks so much for any guidance ...

Answer 1

I would go with this: citat from there: How to save an IPv6-compatible address in a relational database "Final solution: 2xBIGINT, if the second bigint is NULL, then that means IPv4"

Answer 2

It seems your main concern for space. In this case, you could use the fact that the IPv4 addresses are (in essence) 32-bit numbers and IPv6 are 128. The IPv4 address can be stored in the INT column, but IPv6 will require two BIGINT columns in MySQL. This is likely to be much more economical than storing strings.

The cost of this is that you need to do the conversion from the address → number before inserting the value into the database. This will (slightly) increase the CPU load on your web server, so you need to find out where your bottleneck will be and optimize for this.

An added benefit of storing addresses as numbers is that you can have a very efficient index for the column (s), so finding an address will be lightning fast. Indexing varchar columns is very expensive.