what should be the ideal size for storing IPv4, IPv6 addresses as a string in the MySQL database. should varchar(32) be sufficient?
+3
A:
Numerically, an IPv4 address is 32-bit long and IPv6 address is 128-bit long. So you need a storage of at least 16 bytes.
If the "string" you store is an encoding of the address in byte form, then 16 is enough.
Santa
2010-08-11 04:41:52
+2
A:
If you're storing them as strings rather than bit patterns:
IPv4 addresses consist of four 3-digit decimal characters with three . separators, so that only takes 15 characters such as 255.255.255.255.
IPv6 addresses consist of eight 4-digit hex characters with seven : separators, so that takes 39 characters such as 0123:4567:89ab:cdef:0123:4567:89ab:cdef.
paxdiablo
2010-08-11 04:42:19
+4
A:
Assuming textual representation in a string :
- 15 characters for IPv4 (
xxx.xxx.xxx.xxxformat, 12+3 separators) - 39 characters (32 + 7 separators) for IPv6
Those are the maximum length of the string.
Alternatives to storing as string:
- IPv4 is 32-bits, so a MySQL data type that can hold 4 bytes will do, using
INT UNSIGNEDis common along withINT_ATONandINET_NTOAto handle the conversion from address to number, and from number to address
SELECT INET_ATON('209.207.224.40'); -> 3520061480 SELECT INET_NTOA(3520061480); -> '209.207.224.40'
- For IPv6, unfortunately MySQL does not have a data type that is 16 bytes, however one can put the IPv6 into a canonical form, then separate them into 2
BIGINT(8 bytes), this however will use two fields.
Bakkal
2010-08-11 04:42:35
This is incorrect. You can write IPv6 addresses in the formXXXX:XXXX:XXXX:XXXX:XXXX:XXXX:AAA.BBB.CCC.DDD where the last part is an embedded ipv4 style address. The length is 45 i think?
John Burton
2010-08-11 05:18:48
@John: Egh, no, don't store all the zeroes. IPV4 mapped addresses should be normalized to `::ffff:aaa.bbb.ccc.ddd`. (IPv4 mapped addresses are the only addresses that should appear in that form, and IPv4-mapped addresses always have the first 80 bits as zeroes.)
Nicholas Knight
2010-08-11 05:25:58
@John, I could _write_ it in binary only and it would take up more space but, like your IPv4 bit, it can be _computed_ from the 128-bit address so is not necessary, especially in a DB.
paxdiablo
2010-08-11 05:27:14
Yes sure, you'd only ever use it in that way if you were sensible. But I don't think it's actually illegal to use for random ipv6 addresses so you'd want to be able to store it in your database. You could normalize the addresses of course to be all hex. But I still think it's right to point out that legally an ipv6 address represented as a string can be 45 characters,
John Burton
2010-08-11 05:34:59
@John, I think when embedding the IPv6 is still 128 bits, with the last 32 bits (96-128) representing the embedded IPv4.
Bakkal
2010-08-11 05:35:37
@Bakkal yes it's still 128 bits, and you can rewrite it as a hex address. But 2001:08b0:FFFF:FFFF:FFFF:FFFF:255.255.255.255 would be a legal (if stupid) way to write an ip address. So you should be aware that you can legally write an ipv4 address as a string that is 45 characters long. Whether you choose to allow that in your database, or if you choose to normalize addresses is up to you.
John Burton
2010-08-11 05:39:24
@John: RFC 2373 doesn't explicitly forbid it, but when it defines the support for dotted-quad, it does so in the context of IPv4 transition. I would very much consider it a bug if that form is being generated in any other context. Certainly the standard socket APIs don't call for it.
Nicholas Knight
2010-08-11 05:50:41
@Nicholas Indeed. I just wanted to point out that technically you do need 45 bytes to store any valid representation of an ipv6 address as a string. However I entirely agree that you'd never actually do this.
John Burton
2010-08-11 06:14:40
A:
Assuming you don't have any network information (such as LL identifier, class, or CIDR mask) attached, an IPv4 address is up to fifteen characters (4x3 numbers+3 periods) and an IPv6 address may be up to 39 characters.
Borealid
2010-08-11 04:43:45
A:
- do not store IP address as a string
- do not mix ipv6 addresses with ipv4 ones
- ipv6 is a matter of distant future
- ipv4 address length is 4 bytes, thus, common INT unsigned is enough and the only possible option for the ipv4.
- if you really plan to use ipv6, 2 bigint unsigned fields is the option.
Col. Shrapnel
2010-08-11 04:58:26
IPv4 exhaustion is about two years off, depending whose statistics you believe (http://www.potaroo.net/tools/ipv4/ looks pretty credible), so I wouldn't be saying IPv6 is a matter for the distant future. But I agree with everything else you say.
Zack
2010-08-11 05:02:33
I access google, my email, rss news reader, youtube, facebook, my isp's services, irc, my world of warcraft guild website all via ipv6. My PCs use ipv6 locally by default. My *phone* talks to those websites doing ipv6.... Guess I'm living in the future then! Yay :)
John Burton
2010-08-11 05:08:15
Everything I do these days assumes I may have to deploy on IPv6 at any moment. I've already lived through a fire drill on this once -- a former employer was a Comcast vendor and ran out the clock on Comcast's mandate that IPv6 be supported by X date. The operations manager had to hack the final bits together at the last minute.
Nicholas Knight
2010-08-11 05:09:25
IPv4 exhaustion is like petroleum exhaustion. years of talking of. Real move to ipv6 will require shitload of work, not that lame "what max length I need".
Col. Shrapnel
2010-08-11 05:10:07
Furthermore, there is no reason not to mix IPv6 and IPv4 addresses, they mix just fine. The entire IPv4 address space is a subset of the IPv6 address space, just store in IPv6 form and convert as needed.
Nicholas Knight
2010-08-11 05:11:02
@Col: All code I write supports IPv6. Why the hell doesn't yours?
Nicholas Knight
2010-08-11 05:11:39
1/ Do not dictate to others (especially without backing it up with reasons). Maybe they _want_ to search on the textual representation. 2/ Why not? There are networks here _now_ that use both. 3/ IPv6 is being used _now_. _Here_. 4/ See 1. 5. See 1. 6/ Please don't be offended, this comment is just my advice. I'm not going to vote you down since it looks like you've been kicked around enough already :-) That's probably retribution since all the text-storage answers have a -1 on them so it's a fair, if not perfect, assumption they come from you.
paxdiablo
2010-08-11 05:20:00
@Nicholas I can stand ipv6 stored along with ipv4 if a latter one being converted. I am talking of mixing lame string representations in the same column
Col. Shrapnel
2010-08-11 05:21:10
@pax "Maybe they want to search on the textual representation". most common lame excuse. "I do not want to learn, I want to do it lame way". I can stand such an attitude anywhere beside SO that claimed itself as a most pro resource.
Col. Shrapnel
2010-08-11 05:22:41
IPv4 exhaustion *is* like petroleum exhaustion: complete exhaustion may well never happen, because as we get closer the market price will increase to compensate. IPv6, in comparison, is a virtually unlimited (and hence extremely cheap) resource. Why wouldn't you want to at least be able to use the cheap one? Especially since so much of the major infrastructure has already upgraded.
Ken
2010-08-11 05:23:59
Col: SO has never claimed to be exclusively a pro resource. The first sentence of the FAQ says "SO is for professional *and enthusiast* programmers". If you want to only talk to pros, SO is not the site for you.
Ken
2010-08-11 05:26:57
@Ken: ICANN/IANA, the RIRs, and much of the internet community are acting to ensure that IPv4 cannot be turned into the kind of market you envisage.
Nicholas Knight
2010-08-11 05:28:45
You may also want to learn some manners (or at least some tact). Insulting the questioners with "Why disgrace SO with such questions and answers?" is unlikely to win you many friends. Perhaps there was a less confrontational way you could have phrased that. On top of the FAQ stating that SO is for _all_ skill levels, I seem to recall a big bold bit stating "Be nice.".
paxdiablo
2010-08-11 05:31:14
@pax I don't need "many friends". especially of usual SO folks, greedy for reputation, not knowledge. Though your point about rules is sensible.
Col. Shrapnel
2010-08-11 05:40:10
@Ken it claimed to be the ultimate programming knowledge source. and failed.
Col. Shrapnel
2010-08-11 05:41:10
That's fine, some of us need less friends than others :-) However, even in my answers where I've questioned the wisdom of doing something the way the questioner is trying, I tend to lead with something like "Are you sure you want to do it that way? You may have problems with your frobozz corrupting your xyzzy structures. Perhaps it may be better if you ...". I certainly _don't_ lay into them straight away with insults (well, unless I've had a _real_ bad day). Still, I've said my piece, I don't want to extend this any more than I have to. Feel free to have the last word...
paxdiablo
2010-08-11 05:53:29
Ken
2010-08-11 06:09:50
@pax lol i am not arguing with you, so, no point in that "last word" thing :) dont take it too seriously, pal
Col. Shrapnel
2010-08-11 06:22:03
@Ken they claim it. I don't have link atm, but they sure do. something as excuse of discouraging "google it" answers or even MANUAL LINKS, my ass.
Col. Shrapnel
2010-08-11 06:23:39
With computers, "is never going to happen" means "will certainly happen next Thursday at 3AM"; and "far in the future" means "two months from now, clients start asking why it's not implemented already" ;) (yes, it's supposed to be a humorous exaggeration, but as of 2010, IPv6 no longer falls into the YAGNI category)
Piskvor
2010-08-24 08:17:12