What would you recommend as the maximum size for a database column storing client ip addresses? I have it set to 16 right now, but could I get an ip address that is longer than that with IPv6, etc?
+5
A:
If you want to handle IPV6 in standard notation there are 8 groups of 4 hex digits:
2001:0dc5:72a3:0000:0000:802e:3370:73E4
39 characters.
unhillbilly
2009-07-02 21:21:42
good short answer
Omu
2010-04-21 07:39:57
+10
A:
For IPv4, you could get away with storing the 4 raw bytes of the IP address (each of the numbers between the periods in an IP address are 0-255, i.e., one byte). But then you would have to translate going in and out of the DB and that's messy.
IPv6 addresses are 128 bits (as opposed to 32 bits of IPv4 addresses). They are usually written as 8 groups of 4 hex digits separated by colons: 2001:0db8:85a3:0000:0000:8a2e:0370:7334. If you made your field 39 characters you would be completely set for IPv6
Matt Bridges
2009-07-02 21:22:55
Some databases (postgres at least) have a native IP column type, and does the conversion for you.
gnud
2009-07-02 21:32:34
I would avoid mixing IPv4 and IPv6 in the same filed of a database for "a while." IPv4 is still the default standard, and will continue to be used for years to come. In legacy applications I have worked with, when it became necessary to add IPv6 addresses to the database, this was done as a separate entry. This allowed existing code that expected IPv4 address to be left in place, and allowed code to continue to only get an IPv4 address. For new portions of the code, they had an option to specifically get IPv4, IPV6, or a mix from the query.
semiuseless
2009-07-02 22:07:17
If you were going to store IPv4 as a single byte, wouldn't it be more a question of what data structures are accepted by the client that reads? If you were to store it as a single INT, and the client accepted binary, yeah that wouldn't be good...I think the point about having a larger type-of might be a good concern, depending on the number and sophistication of the applications. You might have apps that accept just one format, or both. You might want to have a related field that had a type-of-addressing label.
benc
2009-07-26 20:26:34
+4
A:
As described in the IPv6 Wikipedia article,
IPv6 addresses are normally written as eight groups of four hexadecimal digits, where each group is separated by a colon (:)
A typical IPv6 address:
2001:0db8:85a3:0000:0000:8a2e:0370:7334
This is 39 characters long. IPv6 addresses are 128 bits long, so you could conceivably use a binary(16) column, but I think I'd stick with an alphanumeric representation.
Michael Petrotta
2009-07-02 21:24:05
+1
A:
IPv4 uses 32 bits, in the form of:
255.255.255.255
I suppose it depends on your datatype, whether you're just storing as a string with a CHAR type or if you're using a numerical type.
IPv6 uses 128 bits. You won't have IPs longer than that unless you're including other information with them.
IPv6 is grouped into sets of 4 hex digits seperated by colons, like (from wikipedia):
2001:0db8:85a3:0000:0000:8a2e:0370:7334
You're safe storing it as a 39-character long string, should you wish to do that. There are other shorthand ways to write addresses as well though. Sets of zeros can be truncated to a single 0, or sets of zeroes can be hidden completely by a double colon.
Chet
2009-07-02 21:30:09
+3
A:
Take it from someone who has tried it all three ways... just use a varchar(39)
The slightly less efficient storage far outweighs any benifit of having to convert it on insert/update and format it when showing it anywhere.
Neil N
2009-07-02 21:34:38
A:
If you are just storing it for reference, you can store it as a string, but if you want to do a lookup, for example, to see if the IP address is in some table, you need a "canonical representation." Converting the entire thing to a (large) number is the right thing to do. IPv4 addresses can be stored as a long int (32 bits) but you need a 128 bit number to store an IPv6 address.
For example, all these strings are really the same IP address: 127.0.0.1, 127.000.000.001, ::1, 0:0:0:0:0:0:0:1