56

What is the best field type and length for storing IP addresses in a MySQL database?

What about for IPv6?

Mike
  • 563
  • 1
  • 5
  • 5

6 Answers6

61

Store the ip as a INT(11) UNSIGNED, then use the INET_ATON and INET_NTOA functions to store/retrieve the ip address.

Sample code:

INSERT table(ip) VALUES (INET_ATON('192.168.0.1')); /*ip = 3232235521*/
SELECT INET_NTOA(ip) As IPAddress FROM table; /*IPAddress = 192.168.0.1*/
The Scrum Meister
  • 29,681
  • 8
  • 66
  • 64
  • 6
    On PHP side you can try `ip2long` and `long2ip` functions. – Thai Feb 13 '11 at 06:00
  • I am using $_SERVER['REMOTE_ADDR']. So would I just do "INSERT table(ip) VALUES (INET_ATON($_SERVER['REMOTE_ADDR']));" ? – Mike Feb 13 '11 at 06:02
  • @mike Yes, but **surround it with single quotes** – The Scrum Meister Feb 13 '11 at 06:03
  • @thai `ip2long` returns unsigned numbers – The Scrum Meister Feb 13 '11 at 06:06
  • 19
    I don't think this will support IPv6. – Michael Aaron Safyan Feb 13 '11 at 06:07
  • @michael-aaron-safyan Good point, but by the last statement in the OP *Do the "." count as a character*, it seams that he is using IPV4 – The Scrum Meister Feb 13 '11 at 06:08
  • 4
    Yes, the OP clearly isn't thinking about IPv6. But he should be. – Michael Aaron Safyan Feb 13 '11 at 06:10
  • @Thai: `inet_pton` is also available on PHP. – Alix Axel Feb 13 '11 at 09:55
  • 3
    Why should you be dealing with the INET functions. Basically you are recording an IP address that has been passed to you from the network stack. Just store it as its given to you (hopefully as a string). This way you will not have to change the database schema when IPv8 arrives. – Richard Schneider Feb 14 '11 at 09:33
  • 1
    @richard-schneider The original question was regarding IPv4 – The Scrum Meister Feb 14 '11 at 17:41
  • 2
    @Schneider, IPv6 should last us for the forseeable future (IPv6 is 128-bit, whereas IPv4 was 32-bit, so that's 2^96 times the number of IPv4 addresses), so I don't think IPv8 will be needed in our lifetimes. But IPv6 is here and definitely needs to be supported; the last pool of IPv4 addresses has already been assigned. – Michael Aaron Safyan Feb 15 '11 at 08:28
  • 6
    The `(11)` is not needed. It's simply a hint to mysql as to how many digits you'd like displayed. Internally it'll ALWAYS be a full 32bit integer. – Marc B Apr 12 '11 at 00:30
  • 2
    I really prefer a database thats easy to work with so I dont have to run unnecessary functions to see whats stored in a row. Keep it a string, it'll make the select * readable. If you need to validate your data, validate it before inserting it. I guarantee you can spare the space, if you can't, why are you using MySQL? – Jake Mar 20 '12 at 17:50
  • @RichardSchneider, I see your point, though I can imagine at least two valid use cases for storing IP in the natural form (a sequence of bytes as opposed to the string): 1) you might need to look up addresses by their network prefix to find out how does a particular subset of users break down between countries 2) you have to store A LOT of addresses and are actually concerned about storage space – Septagram Jun 20 '13 at 22:14
  • @richard-schneider Seriously? IPv6 address space is really huge. Will we ever need to move from IPV6 to a bigger address space?(at least in the near future) – Andreas Nov 21 '13 at 22:22
  • Should I use `BIGINT` for storing IPv6? Or what? How can I store IPv6? – stack Nov 21 '15 at 12:51
  • @MarcB What do you mean exactly? When should I write a number in this: `INT(?) UNSIGNED`? And what number? – stack Nov 21 '15 at 12:53
  • @RichardSchneider Do you mean I use `CHAR (x)` instead of `INT (11) UNSIGNED`? Well what number should I have write in this: `CHAR (?)` ? – stack Nov 21 '15 at 13:02
18

It depends on what you want to do with it, but probably the simplest way would be to represent it as a string. And this question covers how many characters would be required to handle it. (It's 45).

Community
  • 1
  • 1
Michael Aaron Safyan
  • 93,612
  • 16
  • 138
  • 200
  • 1
    Yes (+1) right on. Currently we have two forms of an IP address (IPv4 and IPv6). Who knows what new forms will appear in the next 20 years. Just store it as vchar and don't worry. – Richard Schneider Feb 14 '11 at 09:28
  • 1
    Thanks for the support, Richard. I don't think we need to worry beyond IPv6, but support for IPv6 in addition to IPv4 is a must. – Michael Aaron Safyan Feb 15 '11 at 08:29
  • 1
    @RichardSchneider, IPv6 is designed to last beyond 200 years – Pacerier Aug 30 '13 at 09:07
  • 1
    Although pragmatic, one should normalize the format used to store addresses that way. In case of IPv6, one should settle for one defined way of (not) shortening the address by skipping all-zero groups and abbreviating `::`. Otherwise, it would be hard to easily query for identical, but differently rendered, addresses. – ojdo Dec 01 '22 at 15:36
12

If you want support both IPv6 and IPv4 store it as BINARY(16) and convert IP address before inserting it to database. For example, in PHP (langugage commonly used with MySQL) you can do this with inet_pton() function.

user11153
  • 8,536
  • 5
  • 47
  • 50
6

IPv6 addresses are 128 bit (16 byte) so you need a field large enough to store that. Also you may need a field to indicate whether or not the IP is IPv4 or IPv6 (::192.168.4.10 in IPv6 is numerically the same as 192.168.4.10 in IPv4, but depending on your application you may need to differentiate between the two).

If you need to store subnets you might want to store the first address, the CIDR mask, and the calculated upper address (broadcast address) of the subnet. This would help in searches so you could do queries like this

SELECT * FROM networks WHERE lowerBound<=MYIP AND upperBound>=MYIP

If you are using subnets you should also calculate that lower bound yourself and not just rely on the user doing it correctly (pseudo code):

lowerBound = AND(networkAddress, subnetMask)
upperBound = OR(lowerBound, complement(subnetMask))

This applies for both IPv4 and IPv6.

JasonCG
  • 889
  • 13
  • 21
4

If you plan on searching the database by IP address then an INT would be much faster to query. If you are just storing for display (e.g. in a log output) then a string would be better since you don't need to convert it back and forth.

Chris M
  • 41
  • 1
0

I fully agree with Scrum Meister that the best way is to use INT(11) UNSIGNED with storage/retrieval in the INET functions, but sometimes if you're not going to be querying by subnet you may opt for VARCHAR(45)

philwinkle
  • 7,036
  • 3
  • 27
  • 46
  • Why should you be dealing with the INET functions. Basically you are recording an IP address that has been passed to you from the network stack. Just store it as its given to you (hopefully as a string). This way you will not have to change the database schema when IPv8 arrives. – Richard Schneider Feb 14 '11 at 09:31
  • I agree - the 'best' way in network practice would be to use INET functions so you can query by *subnet*... if you don't need this sort of functionality, your best bet is store as a string that's large enough to accomodate IPv6. – philwinkle Feb 15 '11 at 04:19