php 在数据库中存储 IP 的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4982701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to store IP in database?
提问by Mike
What is the best field type and length for storing IP addresses in a MySQL database?
在 MySQL 数据库中存储 IP 地址的最佳字段类型和长度是多少？
What about for IPv6?
IPv6 呢？
回答by The Scrum Meister
Store the ip as a INT(11) UNSIGNED, then use the INET_ATONand INET_NTOAfunctions to store/retrieve the ip address.
将 ip 存储为INT(11) UNSIGNED，然后使用INET_ATON和INET_NTOA函数来存储/检索 ip 地址。
Sample code:
示例代码：
INSERT table(ip) VALUES (INET_ATON('192.168.0.1')); /*ip = 3232235521*/
SELECT INET_NTOA(ip) As IPAddress FROM table; /*IPAddress = 192.168.0.1*/
回答by Michael Aaron Safyan
It depends on what you want to do with it, but probably the simplest way would be to represent it as a string. And this questioncovers how many characters would be required to handle it. (It's 45).
这取决于你想用它做什么，但最简单的方法可能是将它表示为一个字符串。而这个问题，盖多少个字符将需要处理它。（它是 45）。
回答by user11153
If you want support both IPv6 and IPv4 store it as BINARY(16)and convert IP address before inserting it to database. For example, in PHP (langugage commonly used with MySQL) you can do this with inet_pton()function.
如果您希望同时支持 IPv6 和 IPv4，则BINARY(16)在将其插入数据库之前将其存储为并转换 IP 地址。例如，在 PHP（MySQL 常用的语言）中，您可以使用inet_pton()函数执行此操作。
回答by JasonCG
IPv6 addresses are 128 bit (16 byte) so you need a field large enough to store that. Also you may need a field to indicate whether or not the IP is IPv4 or IPv6 (::192.168.4.10 in IPv6 is numerically the same as 192.168.4.10 in IPv4, but depending on your application you may need to differentiate between the two).
IPv6 地址是 128 位（16 字节），因此您需要一个足够大的字段来存储它。此外，您可能需要一个字段来指示 IP 是 IPv4 还是 IPv6（IPv6 中的 ::192.168.4.10 在数字上与 IPv4 中的 192.168.4.10 相同，但根据您的应用程序，您可能需要区分两者） .
If you need to store subnets you might want to store the first address, the CIDR mask, and the calculated upper address (broadcast address) of the subnet. This would help in searches so you could do queries like this
如果您需要存储子网，您可能需要存储子网的第一个地址、CIDR 掩码和计算出的上位地址（广播地址）。这将有助于搜索，因此您可以进行这样的查询
SELECT * FROM networks WHERE lowerBound<=MYIP AND upperBound>=MYIP
If you are using subnets you should also calculate that lower bound yourself and not just rely on the user doing it correctly (pseudo code):
如果您使用子网，您还应该自己计算下限，而不仅仅是依赖用户正确执行（伪代码）：
lowerBound = AND(networkAddress, subnetMask)
upperBound = OR(lowerBound, complement(subnetMask))
This applies for both IPv4 and IPv6.
这适用于 IPv4 和 IPv6。
回答by Chris M
If you plan on searching the database by IP address then an INT would be much faster to query. If you are just storing for display (e.g. in a log output) then a string would be better since you don't need to convert it back and forth.
如果您计划按 IP 地址搜索数据库，那么查询 INT 会快得多。如果您只是为了显示而存储（例如在日志输出中），那么字符串会更好，因为您不需要来回转换它。
回答by philwinkle
I fully agree with Scrum Meister that the bestway is to use INT(11) UNSIGNEDwith storage/retrieval in the INET functions, but sometimes if you're not going to be querying by subnet you may opt for VARCHAR(45)
我完全同意 Scrum Meister最好的方法是INT(11) UNSIGNED在 INET 函数中使用存储/检索，但有时如果你不打算通过子网查询，你可以选择VARCHAR(45)
