13

I want to be able to store both IPv4 and IPv6 addresses in my table. What is the most efficient way to store the IP address of a user regardless of whether it is an IPv4 or IPv6 address?

This will be used in a production environment, so future proof'd suggestions are preferred.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
user2650277
  • 6,289
  • 17
  • 63
  • 132
  • An IPv4 Address is 32-bits long. An IPv6 Address is 128-bits long. For Storage Efficiency IPv6 Addresses can be stored in a `BINARY(16)` column however this is inefficient for IPv4 Addresses which would be better in an `INT UNSIGNED` column. – AeroX Mar 25 '14 at 14:16
  • 1
    Related: http://stackoverflow.com/questions/4444771/how-to-store-a-128-bit-number-in-a-single-column-in-mysql – AeroX Mar 25 '14 at 14:20
  • @AeroX if i use `BINARY(16)` , will i be able to store the ipaddress as is e.g 192.168.1.2 or i need to perform some conversion – user2650277 Mar 25 '14 at 18:03

2 Answers2

11

I would recommend to store every address in IPv6 format. There is an official mapping for that: the IPv4-mapped IPv6 address. It works like this:

Take for example IPv4 address 192.0.2.44
The IPv4-mapped IPv6 address would be ::ffff:192.0.2.44
Which can also be written as ::ffff:c000:022c (192 decimal is c0 hexadecimal, etc)

You can use the inet_pton() function to parse such addresses, and on my local system the inet_ntop() function also outputs them in the most readable format (::ffff:192.0.2.44). That way you only have one format to deal with in your application.

Also see this related answer.

Community
  • 1
  • 1
Sander Steffann
  • 9,509
  • 35
  • 40
  • Do every ipv4 address have a ipv6 equivalent?.Also will i be able to convert those ipv6 addresses back to ipv4.Can you suggest the best datatype and size needed to store ipv6 for mysql – user2650277 Mar 25 '14 at 17:24
  • 1
    Use a BINARY(16) for the IPv6 address, and if you follow the 'related answer' link you will see how to convert back. – Sander Steffann Mar 25 '14 at 17:31
1

Why does it have to be a single column? A few suggestions...

Have 2 columns, one for IPv4, one for IPv6.

Store the IP address in a single column and have another column that basically holds a boolean whether the address is IPv4 or not...

kevcodez
  • 1,261
  • 12
  • 27
  • 5
    It would be a waste of resources since ipaddress isn't that much important.I mean that it would be retrieve from the database only in rare cases.Only one version is needed – user2650277 Mar 25 '14 at 14:11