在 MySQL 中存储 IP 地址的最有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2542011/
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
Most efficient way to store IP Address in MySQL
提问by ensnare
What is the most efficient way to store and retrieve IP addresses in MySQL? Right now I'm doing:
在 MySQL 中存储和检索 IP 地址的最有效方法是什么？现在我正在做：
SELECT * FROM logins WHERE ip = '1.2.3.4'
Where ip is a VARCHAR(15)field.
其中 ip 是一个VARCHAR(15)字段。
Is there a better way to do this?
有一个更好的方法吗？
回答by Daniel Vassallo
For IPv4 addresses, you may want to store them as an int unsignedand use the INET_ATON()and INET_NTOA()functions to return the IP address from its numeric value, and vice versa.
对于IPv4 地址，您可能希望将它们存储为 anint unsigned并使用INET_ATON()和INET_NTOA()函数从其数值返回 IP 地址，反之亦然。
Example:
例子：
SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
| 2130706433 |
+------------------------+
1 row in set (0.00 sec)
SELECT INET_NTOA('2130706433');
+-------------------------+
| INET_NTOA('2130706433') |
+-------------------------+
| 127.0.0.1 |
+-------------------------+
1 row in set (0.02 sec)
回答by Dean Harding
If you only want to store IPv4 addresses, then you can store them in a 32-bit integer field.
如果您只想存储 IPv4 地址，则可以将它们存储在 32 位整数字段中。
If you want to support IPv6 as well, then a string is probably the most easy-to-read/use way (though you could technically store them in a 16-byte VARBINARY()field, it would be annoying trying to generate SQL statements to select by IP address "by hand")
如果您也想支持 IPv6，那么字符串可能是最易于阅读/使用的方式（尽管您可以在技术上将它们存储在 16 字节的VARBINARY()字段中，但尝试生成 SQL 语句以供选择会很烦人） IP地址“手动”）
回答by Mark Byers
The most important thing is to make sure that column is indexed. This could make a huge difference to queries based on IP address.
最重要的是确保该列被索引。这可能会对基于 IP 地址的查询产生巨大影响。
回答by DGM
Whatever is easiest for you to work with. The size or speed issue is not an issue until you know it is an issue by profiling. In some cases, a string might be easier to work with if you need to do partial matching. But as a space or performance issue, don't worry about it unless you have real cause to worry about it.
任何对您来说最容易使用的东西。在您通过分析知道这是一个问题之前，大小或速度问题不是问题。在某些情况下，如果您需要进行部分匹配，字符串可能更容易使用。但作为空间或性能问题，除非您有真正的理由担心，否则不要担心。
回答by Brian Dilley
maybe store the integer value directly in an integer field? An IP address is basically 4 "shorts".
也许将整数值直接存储在整数字段中？一个IP地址基本上是4个“短裤”。
Check it out: http://en.kioskea.net/faq/945-converting-a-32-bit-integer-into-ip
检查一下：http: //en.kioskea.net/faq/945-converting-a-32-bit-integer-into-ip
