开发者

Most efficient way to store IP Address in MySQL [duplicate]

This question already has answers here: 开发者_开发问答 Datatype for storing ip address in SQL Server (12 answers) Closed 5 years ago.

What is the most efficient way to store and retrieve IP addresses in MySQL? Right now I'm doing:

SELECT * FROM logins WHERE ip = '1.2.3.4'

Where ip is a VARCHAR(15) field.

Is there a better way to do this?


For IPv4 addresses, you may want to store them as an int unsigned and use the INET_ATON() and INET_NTOA() functions to return the IP address from its numeric value, and vice versa.

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)


If you only want to store IPv4 addresses, then you can store them in a 32-bit integer field.

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")


The most important thing is to make sure that column is indexed. This could make a huge difference to queries based on IP address.


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.


maybe store the integer value directly in an integer field? An IP address is basically 4 "shorts".

Check it out: http://en.kioskea.net/faq/945-converting-a-32-bit-integer-into-ip

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜