For lovers of storing IP addresses as integers in MySQL tables, there’s a snag when doing this for IPv6 addresses. A 128-bit number is too big to stuff into a BIGINT type. All Fs integer equivalent is 340,282,366,920,938,463,463,374,607,431,768,211,455. An unsigned BIGINT tops out at 18,446,744,073,709,551,615.
These are your options:
- Store it as a VARCHAR(39) in full
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffffform. Which of course, is a horrible idea since you can’t easily sort and it’s prone to garbage-in, garbage-out problems because of the various forms of valid shorthand. (Admittedly, I’ve done this in places with v4 addresses and have almost fully recanted in my code!) - Denial. “IPv6 is just a fad.” “I’ll have a new job before I have to deal with it.”
- Wring your hands. File countless MySQL bug reports asking for native IPv6 and IPv6-capable INET_ATON, INET_NTOA functions. (The former still needs to be added in MySQL 5.x)
- Fan the flames. Proclaim you never had this problem in PostgreSQL because of native data types that handle IPv6 and CIDR, and all MySQL ninnies should escape their new Oracle overlords and convert.
- Split up a v6 address at the 64-bit boundary, stuff the two halves into dual BIGINT columns. You could even go as far as saying if one BIGINT column is null, then it must be a v4 address.
- Store it in a DECIMAL(39). You’ll still need to invent a way to convert the address to integer and back. To do this in perl, Net::IP’s
intip()method will at least give you IPv6 -> integer. To get it back, you’ll have to roll your owninet_ntoa6function. Don’t forget Math::BigInt. Alternatively, here’s some MySQL stored functions for INET_ATON6 and INET_NTOA6. You can either use these directly, or use them as inspiration for your owninet_ntoa6().
Since I have apps that require storing v6 addresses now, I’ve chosen the latter.
