How to store IPv6 in a table?



  • Wondering what strategies there are for storing an IPv6 address in a table column. Ideally compatible across Oracle, MySQL, DB2, etc. Don't want to the naive approach of "xx:xx:xx:xx...."

    If the same column can store IPv4, that would be a plus too.



  • @savar said:

    Wondering what strategies there are for storing an IPv6 address in a table column. Ideally compatible across Oracle, MySQL, DB2, etc. Don't want to the naive approach of "xx:xx:xx:xx...."

    If the same column can store IPv4, that would be a plus too.

    Either a 128-bit unsigned INT (or two 64-bit ones if the DB doesn't support 128-bit INTs, like MySQL) which will hold IPv4 addresses as well (as 32-bit INTs).  PostgreSQL has an "network address" column that will store IPv4/6 addresses with a netmask, but MySQL does not have this.

     

    Two 64-bit INTs should work with most DBs and will also allow you to store IPv4 addrs easily.  It might seem a bit hackish, but it's not bad. 



  • I just wanted to come in here and say that I agree with morbs.  I wouldn't be surprised if more RDBMSes start implementing 128 bit ints for exactly this reason, and create native conversion functions into and out of a human-readable form.

    I know MySQL has such a pair of conversion functions, and they urge you to use 32-bit ints to store IP addresses and use their conversion functions to convert to strings and back.


Log in to reply