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

问题描述

我该怎么做?

现在，将不使用IPv6，但是我需要设计应用程序使其支持IPv6.有必要在MySQL数据库中存储IP地址和CIDR块(也是BGP NLRI，但这是另一回事).我一直将INT用于IPv4，将TINYINT用于masklen，但是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支持"表明它正在要在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屋！