79

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?

ozahorulia
  • 9,798
  • 8
  • 48
  • 72
ensnare
  • 40,069
  • 64
  • 158
  • 224

5 Answers5

122

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)
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 4
    you wouldn't store as `INT` but `INT UNSIGNED`. i like the use of purposely inbuilt functions though. +1 – pstanton Mar 30 '10 at 00:11
  • Thanks. Do you know if there is a python function for this? – ensnare Mar 30 '10 at 00:48
  • @ensnare: Yes, check this: http://snipplr.com/view/14807/convert-ip-to-int-and-int-to-ip/. – Daniel Vassallo Mar 30 '10 at 00:53
  • 21
    What about IPv6? – CMCDragonkai Mar 29 '14 at 06:35
  • 12
    For **IPv6** and **IPv4** use function [INET6_ATON()](http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton) – revoke Feb 20 '16 at 14:15
  • 2
    For IPv6 I got NULL while selecting IP address, later I understood to use INET6_NTOA with HEX() and UNHEX() functions for readability and also, when using this functions, query ignored index on this column. I put my thoughts here http://www.rathishkumar.in/2017/08/how-to-store-ip-address-in-mysql.html – Rathish Kumar B Aug 25 '17 at 16:45
  • Can you please update your answer to use `INET6_ATON()` ? or you don't recommend `INET6_ATON()` ? – Accountant م May 09 '18 at 09:42
  • @rathishDBA it's blob, not null – Accountant م May 09 '18 at 10:02
  • @CMCDragonkai the most efficient storage for both ipv4 and ipv6 is probably VARBINARY(16), i'm not 100% sure but it would make sense =/ (an ipv4 address is 4 bytes long, and an ipv6 address is 16 bytes) – hanshenrik Aug 29 '19 at 09:32
64

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

Dean Harding
  • 71,468
  • 13
  • 145
  • 180
  • 30
    +1: the only answer that predicts usage of IPv6 – Juliano Mar 30 '10 at 00:02
  • 3
    Just when using IPV6 the functions are different ( INET6_ATON(ip) ). IPV4 needs VARBINARY(4) and IPV6 would need VARBINARY(16) (which of course works for V4 addresses as well). You can convert a V4 IP (INT) to a compatible format like this: INET6_ATON(INET_NTOA(ip)) – John Sep 24 '14 at 14:35
  • 4
    `INET6_ATON('172.32.x.x')` can convert both IPv4 and IPv6 addresses into `blob`. The same can be converted back using `INET6_NTOA`. No need to do `INET6_ATON(INET_NTOA(ipv4))` as stated by @John – Tirtha R Apr 10 '18 at 19:12
  • @TirthaR correction: not into `BLOB`, but into `VARBINARY(16)` :) – Lukasz032 Aug 17 '23 at 11:40
4

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

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
4

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.

DGM
  • 26,629
  • 7
  • 58
  • 79
1

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

Brian Dilley
  • 3,888
  • 2
  • 24
  • 24
  • so he would store it in four fields? and they would be bytes, no? 2^8 everytime Alternatively, he could convert them to decimal numbers and store the decimal, although that would be a larger field, it would be a single field. – jcolebrand Mar 29 '10 at 23:55
  • 1
    A more appropriate vernacular would be to say that an IP address is 4 bytes. – Spencer Ruport Mar 29 '10 at 23:58
  • I got the gist. Brian's saying that conceptually an int is made up of four shorts. A short is a byte. An int is 4 bytes. As said elsewhere on this page, it needs to be an unsigned int. – joe Feb 29 '16 at 23:59