Size for storing IPv4, IPv6 addresses as a string

Question

what should be the ideal size for storing IPv4, IPv6 addresses as a string in the MySQL database. should varchar(32) be sufficient?

@Col: There's no good alternative in any current stable MySQL release. — Nicholas Knight, Aug 11 '10 at 05:12
at least 2 bigints is alternative for the ipv6. and go convert from string ipv4 to string ipv6 on the fly, as it supposed by the question and the answers — Your Common Sense, Aug 11 '10 at 05:14
@Col: How is that better? You still have to convert in your application code, and any human having to dive into the database itself can't actually READ the friggin' addresses because there's no built-in INET_NTOA implementation for IPv6. — Nicholas Knight, Aug 11 '10 at 05:16
@Nicholas you still can use a binary math on the address. Or just BETWEEN to find a range for example. Go try it with string addresses. — Your Common Sense, Aug 11 '10 at 05:24
@Col: It's pretty darn easy to do whatever you want with properly normalized string addresses. All you lose is performance, and it's pretty unlikely the OP is going to be doing enough of those kinds of queries to matter that much. — Nicholas Knight, Aug 11 '10 at 05:34
"All you lose is performance". Excellent! I cannot beat such a piece of wisdom :) — Your Common Sense, Aug 11 '10 at 05:48
possible duplicate of [Maximum length of the textual representation of an IPv6 address?](http://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address) — Dave Jarvis, Dec 28 '12 at 20:36

score 85 · Accepted Answer · edited Mar 14 '20 at 07:48

85

Assuming textual representation in a string :

15 characters for IPv4 (xxx.xxx.xxx.xxx format, 12+3 separators)
45 characters 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 UNSIGNED is common along with INET_ATON and INET_NTOA to 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.

edited Mar 14 '20 at 07:48

roberto carlos

83
1
9

answered Aug 11 '10 at 04:42

bakkal

54,350
12
131
107

15

This is incorrect. You can write IPv6 addresses in the form XXXX: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? – jcoder Aug 11 '10 at 05:18
1

@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 Aug 11 '10 at 05:25
2

@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 Aug 11 '10 at 05:27
2

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, – jcoder Aug 11 '10 at 05:34
1

@John, I think when embedding the IPv6 is still 128 bits, with the last 32 bits (96-128) representing the embedded IPv4. – bakkal Aug 11 '10 at 05:35
@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. – jcoder Aug 11 '10 at 05:39
@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 Aug 11 '10 at 05:50
1

@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. – jcoder Aug 11 '10 at 06:14
1

You could use `VARBINARY(16)` for ipv6 – Evert Sep 25 '18 at 17:58

score 9 · Answer 2 · answered Aug 11 '10 at 04:42

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.

score 8 · Answer 3 · answered Aug 11 '10 at 04:41

8

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.

answered Aug 11 '10 at 04:41

Santa

11,381
8
51
64

score 4 · Answer 4 · answered Apr 08 '13 at 10:05

the ipv6 address maybe 46 characters.

reference: IPv4-mapped IPv6 addresses Hybrid dual-stack IPv6/IPv4 implementations recognize a special class of addresses, the IPv4-mapped IPv6 addresses. In these addresses, the first 80 bits are zero, the next 16 bits are one, and the remaining 32 bits are the IPv4 address. One may see these addresses with the first 96 bits written in the standard IPv6 format, and the remaining 32 bits written in the customary dot-decimal notation of IPv4. For example, ::ffff:192.0.2.128 represents the IPv4 address 192.0.2.128. A deprecated format for IPv4-compatible IPv6 addresses was ::192.0.2.128.[61]

score 3 · Answer 5 · answered Mar 28 '12 at 13:57

You can use a VARBINARY(16) to store an IPv6 address in a binary format.

The applications that need to use this data can then use their inet_pton/ntop implementations to manipulate this data, or you can install a UDF like the one at http://labs.watchmouse.com/2009/10/extending-mysql-5-with-ipv6-functions/

score 2 · Answer 6 · answered Feb 21 '14 at 15:49

Besides what was already said there is Link-Local IPv6 address. If you want to keep the address so that you can use the string to create connections you will need also to keep scope id. On Windows it is a 16-bits number, on Linux it can be a string - interface name, I did not find the maximum length of an interface name.

score 2 · Answer 7 · answered Aug 11 '10 at 04:43

2

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.

answered Aug 11 '10 at 04:43

Borealid

95,191
9
106
122

Size for storing IPv4, IPv6 addresses as a string

7 Answers7

Linked

Related