存储/检索 IPv4 和 IPv4 MySQL 中的 IPv6 地址
抱歉，我对这个主题了解不多，但我正在寻找一个快速简便的解决方案，用于唯一地表示 MySQL 中的任何 IP 地址（v4/v6），以便我可以轻松检索最后一次（如果有） ）某台特定计算机访问了我的网站。
我不需要对地址进行任何计算，只需检索，因此任何唯一的表示都应该没问题。我确实计划存储很多这些（还没有估计），所以空间可能会成为一个问题。
我见过许多存储 IP 地址的解决方案，但不清楚哪一个适用于这两个版本。 MySQL内置的INET_ATON似乎不支持IPv6。 PHP 的 inet_pton 看起来很有前途，但需要事先了解地址格式。我也不确定它的用法（MySQL 字段类型和通过 PHP 编写插入语句）。我见过 varchar(39) 用于将 IPv6 地址表示为字符串，并且我喜欢这个解决方案在某种程度上独立于服务器配置；然而，我对磁盘空间有点不安。对于 $_SERVER['HTTP_CLIENT_IP'] 可能输出的所有地址来说，这种方法是否足够？
我有点惊讶没有一个明显的通用解决方案。我认为这是一项非常常见的任务。我对这个问题犹豫不决，想继续我的项目。快速简单的解决方案不合理吗？
非常感谢您的指导...
Sorry I don't know much about this subject, but all I'm looking for is a quick-and-easy solution for uniquely representing any IP address (v4/v6) in MySQL so I can easily retrieve the last time (if any) that a particular computer has visited my site.
I don't need to do any computations on the addresses, just retrieval, so any unique representation should be ok. I do plan on storing a lot of these (don't have an estimate yet), so space may become a concern.
I've seen many solutions for storing IP addresses, but it's unclear which work for both versions. MySQL's built-in INET_ATON doesn't seem to support IPv6. PHP's inet_pton seems promising but requires prior knowledge of the address's format. I'm also unsure about its usage (MySQL field type and writing the insertion statement via PHP). I've seen varchar(39) used to represent IPv6 addresses as strings, and I like that this solution is somewhat independent of server configuration; however, I'm a little uneasy about disk space. Would this approach be sufficient for all addresses that $_SERVER['HTTP_CLIENT_IP'] might output?
I'm a little surprised there isn't an obvious generic solution. I assumed this was a very common task. I'm having indecision about this single issue, and would like to move on with my project. Is a quick-and-easy solution unreasonable?
Thanks very much for any guidance...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会这样做：来自那里的citat：如何在关系数据库中存储 IPv6 兼容地址“最终决定：2xBIGINT 如果第二个 bigint 为 NULL，则表示 IPv4”
I would go with this: citat from there : How to store IPv6-compatible address in a relational database "Final decision taken: 2xBIGINT if the second bigint is NULL, then it means IPv4"
听起来您主要关心的是空间。如果是这种情况，那么您可以使用以下事实：IPv4 地址（本质上）是 32 位数字，而 IPv6 是 128 位数字。IPv4 地址可以存储在
INT列中，但 IPv6 需要两个MySQL 中的BIGINT列。这可能比存储字符串更节省空间。这样做的代价是你需要进行从地址 -> 的转换。将值插入数据库之前的编号。这会（稍微）增加 Web 服务器上的 CPU 负载，因此您需要找出瓶颈所在并对此进行优化。
将地址存储为数字的另一个好处是，您可以在列上建立非常有效的索引，因此查找地址将快如闪电。为
varchar列建立索引非常昂贵。It sounds like your main concern is about space. If that's the case, then you could use the fact that IPv4 addresses are (essentially) 32-bit numbers and IPv6 are 128. An IPv4 address can be stored in an
INTcolumn, but IPv6 would require twoBIGINTcolumns in MySQL. This is likely to be much more space-efficient than storing strings.The cost of doing this is that you need to do the conversion from address -> number before inserting the value into the database. That will (slightly) increase CPU load on your web server, so you need to figure out where your bottleneck is going to be and optimise for that.
An added benefit of storing the addresses as numbers is that you can have a very efficient index on the column(s) so looking up an address will be lightning fast. Indexing
varcharcolumns is very expensive.