在 MySQL 中存储 IPv6 地址

Storing IPv6 Addresses in MySQL

提问by atx

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.

正如“需要支持ipv6 的 inet_aton 和 inet_ntoa 函数”中所要求的那样，目前没有用于存储 IPv6 地址的 MySQL 函数。用于存储/插入的推荐数据类型/函数是什么？（我不打算将它们存储为字符串）。我也不想将 IPv6 地址分成 2 个 INT。

回答by snap

How about:

怎么样：

BINARY(16)

That should be effective enough.

那应该足够有效了。

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 服务器中将文本 IPv6 地址从/到二进制转换的功能，如该错误报告中所述。您要么需要在应用程序中执行此操作，要么可能需要在 MySQL 服务器中创建一个 UDF（用户定义函数）来执行此操作。

UPDATE:

更新：

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

MySQL 5.6.3 支持 IPv6 地址，请参阅以下内容：“ INET6_ATON(expr)”。

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.

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

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

旧版本 MySQL 和 MariaDB 的实现，请参见以下内容：“ EXTENDING MYSQL 5 WITH IPV6 FUNCTIONS”。

回答by John

No one has posted a full working answer (and lots of examples use the Windows ::1which can be verymisleading for live (or "production") environments) any where (at least that I can find) so here is:

没有人发布完整的工作答案（并且许多示例使用 Windows ::1，这对于实时（或“生产”）环境可能非常误导）任何地方（至少我能找到）所以这里是：

1. The format to store with.
2. Example INSERTquery using a reasonably complex IPv6 IP address.
3. Example SELECTquery that you will be able to echothe IPv6 IP address back to the client.
4. Troubleshooting to ensure you haven't missed any legacy code.

1. 要存储的格式。
2. INSERT使用相当复杂的 IPv6 IP 地址的示例查询。
3. 示例SELECT查询您将能够echo将 IPv6 IP 地址返回给客户端。
4. 故障排除以确保您没有遗漏任何遗留代码。

I changed all the column names to ipv6to reflect that they properlysupport IPv6 (and that allows you to keep the old column ipintact). It is possible to store the ipcolumn in the ipv6column and then just DROPthe ipcolumn once you're certain the conversion has worked; when I actually have time I'll add that to this post.

我将所有列名称更改为ipv6以反映它们正确支持 IPv6（并且允许您保持旧列ip完整无缺）。它可以存储ip在列ipv6列，然后就DROP在ip一旦你一定的转换工作已列; 当我真正有时间时，我会将其添加到这篇文章中。

IPv6 Data Type

IPv6 数据类型

As has been mentioned VARBINARY 16is the desirable way to go until AMD blesses us with 128 bit CPUs and the databases are updated to support 128 bit integers (did I say that correctly?). IPv6 is 128 bit, not 64 bit.

如前所述，这VARBINARY 16是一种理想的方式，直到 AMD 为我们提供 128 位 CPU 并且更新数据库以支持 128 位整数（我说得对吗？）。IPv6 是 128 位，而不是 64 位。

CREATE TABLE `example`
(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ipv6` VARBINARY(16) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_520_ci'
ENGINE=InnoDB;

---

---

IPv6 INSERT Query

IPv6 插入查询

We obviously need to store an IPv6 IP address before we can SELECTit; here is the PHP / SQLcode:

显然，我们需要先存储一个 IPv6 IP 地址SELECT；这是PHP/SQL代码：

$ipv6 = mysqli_real_escape_string($db,'FE80:0000:0000:0000:0202:B3FF:FE1E:8329');
$query1 = "INSERT INTO example (ipv6) VALUES (INET6_ATON('$ipv6'));";

---

---

IPv6 SELECT Query

IPv6 SELECT 查询

The PHP / SQLcode:

在PHP / SQL代码：

$ipv6 = mysqli_real_escape_string($db,'FE80:0000:0000:0000:0202:B3FF:FE1E:8329');
$query2 = "SELECT INET6_NTOA(ipv6) AS ipv6 FROM example WHERE ipv6=INET6_ATON('$ipv6');";

This will return fe80::202:b3ff:fe1e:8329; no, not the full IPv6 (which is FE80:0000:0000:0000:0202:B3FF:FE1E:8329), it's a condensed / shorthand version. There is code to make it the formal full-length version but this is to save myself and others time because thisQ/A is the one that keeps coming up.

这将返回fe80::202:b3ff:fe1e:8329；不，不是完整的 IPv6（即FE80:0000:0000:0000:0202:B3FF:FE1E:8329），它是精简版/速记版。有代码可以使它成为正式的完整版本，但这是为了节省我自己和其他人的时间，因为这个Q/A 是不断出现的问题。

Important:just because someIPv6 addresses looklike they'd fit in to bigintdoes notimply two minutes later someone with a larger IPv6 address won't stop by and wreak havoc.

重要提示：仅仅因为一些IPv6地址看起来就像他们会适合于bigint不不意味着两分钟后，有人用一个更大的IPv6地址不会被和肆虐停止。

Hopefully this will save some folks from the insanity of opening another two dozen tabs. When I have time in the future I'll add the extra PHP code that extends the condensed IPv6 to the full formal format.

希望这能让一些人免于打开另外两打标签的疯狂。将来有时间时，我将添加额外的 PHP 代码，将精简的 IPv6 扩展为完整的正式格式。

---

---

Troubleshooting

故障排除

If for some reason storing and/or retrieving IPv6 addresses is not working for you then grab yourself a copy of Advanced Find and Replace(works faster in Wine than Linux's native grep); use this predominantlyfor finding, not replacing. Ensure that your code is consistenteverywhere in your software.

如果由于某种原因存储和/或检索 IPv6 地址对您不起作用，那么请自己获取一份高级查找和替换（在 Wine 中比 Linux 本地工作更快grep）；使用此主要是寻找，而不是取代。确保您的代码在软件中的任何地方都是一致的。

• All $ipvariables mustbe converted to $ipv6so you know you've got that bit covered.
• Do not forget to remove the ending )for the next four steps:
• Search for all instances of PHPinet_pton(functions and remove them.
• Search for all instances of PHPinet_ntop(functions and remove them.
• Search for all instances of SQLINET_ATON(functions and remove them.
• Search for all instances of SQLINET_NTOA(functions and remove them.
• Search for all instances of $ipv6and ensure that all IP-IN-TO-SQL instances use INET6_ATON('$ipv6')and that all instances where IP-FROM-SQL use INET6_NTOA(ipv6) AS ipv6.
• Search for all instances of $row1['ip']and replace them with $row1['ipv6'].
• Ensure that all instances of $ipv6 =use the following code (with your database object reference changed): $ipv6 = (isset($_SERVER['REMOTE_ADDR']) && strlen($_SERVER['REMOTE_ADDR']) > 0) ? mysqli_real_escape_string($db,$_SERVER['REMOTE_ADDR']) : mysqli_real_escape_string($db,getenv('REMOTE_ADDR'));.
• Ensure that your tests use freshlytested IP addresses instead of potentially botched versions if you are aware that there was something wrong beforeyou started debugging.

• 所有$ip变量都必须转换为 ，$ipv6以便您知道您已经涵盖了这一点。
• 不要忘记删除)接下来四个步骤的结尾：
• 搜索PHPinet_pton(函数的所有实例并删除它们。
• 搜索PHPinet_ntop(函数的所有实例并删除它们。
• 搜索SQLINET_ATON(函数的所有实例并将其删除。
• 搜索SQLINET_NTOA(函数的所有实例并将其删除。
• 搜索 的所有实例$ipv6并确保所有 IP-IN-TO-SQL 实例使用INET6_ATON('$ipv6')以及 IP-FROM-SQL 使用的所有实例INET6_NTOA(ipv6) AS ipv6。
• 搜索 的所有实例$row1['ip']并将其替换为$row1['ipv6']。
• 确保所有实例都$ipv6 =使用以下代码（更改您的数据库对象引用）：$ipv6 = (isset($_SERVER['REMOTE_ADDR']) && strlen($_SERVER['REMOTE_ADDR']) > 0) ? mysqli_real_escape_string($db,$_SERVER['REMOTE_ADDR']) : mysqli_real_escape_string($db,getenv('REMOTE_ADDR'));.
• 如果您在开始调试之前意识到有问题，请确保您的测试使用新测试的 IP 地址，而不是潜在的拙劣版本。

回答by DoesEatOats

Excellent example however I noted the following. That only works if one's version of mysqlhas as function for INET6_ATON. Otherwise the error message might be something like: That FUNCTIONDOESNOTEXIST.

很好的例子但是我注意到了以下内容。仅当一个人的版本mysql具有 as 函数时才有效INET6_ATON。否则，错误消息可能类似于： That FUNCTIONDOESNOTEXIST。