Most efficient way to store IP Address in MySQL [duplicate]
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
精彩评论