sql - Storing IPv6 Addresses in MySQL

Question

As has been requested in "ipv6-capable inet_aton and inet_ntoa functions needed", there is currently no MySQL function for storing IPv6 addresses. What would be the recommended data type/function for storing/inserting? (I don't intend to store them as a string). I also don't want to separate the IPv6 address into 2 INT's.

Answer 1

How about:

BINARY(16)

That should be effective enough.

Currently there is no function to convert textual IPv6 addresses from/to binary in the MySQL server, as noted in that bug report. You either need to do it in your application or possibly make a UDF (User-Defined Function) in the MySQL server to do that.

UPDATE:

MySQL 5.6.3 has support for IPv6 addresses, see the following: "INET6_ATON(expr)".

The data type is VARBINARY(16) instead of BINARY(16) as I suggested earlier. The only reason for this is that the MySQL functions work for both IPv6 and IPv4 addresses. BINARY(16) is fine for storing only IPv6 addresses and saves one byte. VARBINARY(16) should be used when handling both IPv6 and IPv4 addresses.

An implementation for older versions of MySQL and MariaDB, see the following: "EXTENDING MYSQL 5 WITH IPV6 FUNCTIONS".