在 MySQL 中存储 IPv6 地址

2021-11-20 00:00:00 sql mysql

正如需要支持 ipv6 的 inet_aton 和 inet_ntoa 函数",目前没有用于存储 IPv6 地址的 MySQL 功能.用于存储/插入的推荐数据类型/函数是什么?(我不打算将它们存储为字符串).我也不想将 IPv6 地址分成 2 个 INT.

As has been requested in "ipv6-capable inet_aton and inet_ntoa functions needed", there is currently no MySQL function for storing IPv6 addresses. What would be the recommended data type/function for storing/inserting? (I don't intend to store them as a string). I also don't want to separate the IPv6 address into 2 INT's.

推荐答案

怎么样:

BINARY(16)

那应该足够有效了.

目前没有在 MySQL 服务器中将文本 IPv6 地址从/到二进制转换的功能,如该错误报告中所述.您要么需要在您的应用程序中执行此操作,要么可能需要在 MySQL 服务器中创建一个 UDF(用户定义函数)来执行此操作.

Currently there is no function to convert textual IPv6 addresses from/to binary in the MySQL server, as noted in that bug report. You either need to do it in your application or possibly make a UDF (User-Defined Function) in the MySQL server to do that.

更新:

MySQL 5.6.3 已支持 IPv6 地址,请参阅以下内容:"INET6_ATON(expr)".

MySQL 5.6.3 has support for IPv6 addresses, see the following: "INET6_ATON(expr)".

数据类型是 VARBINARY(16) 而不是我之前建议的 BINARY(16).唯一的原因是 MySQL 函数适用于 IPv6 和 IPv4 地址.BINARY(16) 适用于仅存储 IPv6 地址并节省一个字节.VARBINARY(16) 在处理 IPv6 和 IPv4 地址时应使用.

The data type is VARBINARY(16) instead of BINARY(16) as I suggested earlier. The only reason for this is that the MySQL functions work for both IPv6 and IPv4 addresses. BINARY(16) is fine for storing only IPv6 addresses and saves one byte. VARBINARY(16) should be used when handling both IPv6 and IPv4 addresses.

旧版本 MySQL 和 MariaDB 的实现,请参见以下内容:使用 IPV6 功能扩展 MYSQL 5".

An implementation for older versions of MySQL and MariaDB, see the following: "EXTENDING MYSQL 5 WITH IPV6 FUNCTIONS".

相关文章