如何在关系数据库中存储兼容 IPv6 的地址 [英] How to store IPv6-compatible address in a relational database

问题描述

我该怎么做?

目前不会使用 IPv6，但我需要设计应用程序以使其支持 IPv6.有必要在 MySQL 数据库中存储 IP 地址和 CIDR 块(也是 BGP NLRI，但这是另一回事).我一直对 IPv4 使用 INT + 对 masklen 使用 TINYINT，但 IPv6 是 128 位.

Right now, IPv6 will not be used, but I need to design the application to make it IPv6-ready. It is necessary to store IP addresses and CIDR blocks (also BGP NLRI, but this is another story) in a MySQL database. I've alway used an INT for IPv4 + a TINYINT for masklen, but IPv6 is 128 bit.

什么方法最适合这种情况?2xBIGINT?CHAR(16) 用于二进制存储?CHAR(39) 用于文本存储?8xSMALLINT 在专用表中?

What approach will be best for that? 2xBIGINT? CHAR(16) for binary storage? CHAR(39) for text storage? 8xSMALLINT in a dedicated table?

你会推荐什么?

推荐答案

我不确定哪个是 MySQL 的正确答案，因为它本身还不支持 IPv6 地址格式(尽管而WL#798: MySQL IPv6 support"表明它正在运行在 MySQL v6.0 中，当前文档不支持.

I'm not sure which is the right answer for MySQL given that it doesn't yet support IPv6 address formats natively (although whilst "WL#798: MySQL IPv6 support" suggests that it was going to be in MySQL v6.0, current documentation doesn't back that up).

但是，在您提出的那些中，我建议使用 2 * BIGINT，但请确保它们是未签名的.在 IPv6 中的/64 地址边界处有一种自然分割(因为/64 是最小的网络块大小)，这将很好地与此对齐.

However of those you've proposed I'd suggest going for 2 * BIGINT, but make sure they're UNSIGNED. There's a sort of a natural split at the /64 address boundary in IPv6 (since a /64 is the smallest netblock size) which would align nicely with that.

这篇关于如何在关系数据库中存储兼容 IPv6 的地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！