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.