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

Question

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?

Answer 1

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) 

Answer 2

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