Take the 2-minute tour ×

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

share|improve this question
@Col: There's no good alternative in any current stable MySQL release. –  Nicholas Knight Aug 11 '10 at 5: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 5: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 5: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 5: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 5:34
show 2 more comments

7 Answers

up vote 30 down vote accepted

Assuming textual representation in a string :

  • 15 characters for IPv4 (xxx.xxx.xxx.xxx format, 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 UNSIGNED is common along with INT_ATON and INET_NTOA to handle the conversion from address to number, and from number to address
        -> 3520061480

SELECT INET_NTOA(3520061480);
        -> ''
  • 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.
share|improve this answer
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 5:18
@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 5:25
@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 5:27
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 5:34
@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 5:35
show 3 more comments

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.

share|improve this answer
add comment

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

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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/

share|improve this answer
add comment

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: represents the IPv4 address A deprecated format for IPv4-compatible IPv6 addresses was ::[61]

share|improve this answer
add comment

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.

share|improve this answer
I would suggest meta.stackoverflow.com for this kind of questions –  bboymaanu Feb 21 at 16:17
add comment

Your Answer


