More and more people are using IPv6 on their systems, and this longer format requires a larger datatype for storage in MySQL. IPv4 addresses can be saved in a MySQL or MariaDB UNSIGNED column with the INET_ATON() function. If you put an IPv6 address into this function though, it will return NULL indicating unrecognised (you'll see this as the address 0.0.0.0 if you're displaying it with INET_NTOA()).

The ideal solution is to use the new INET6_ATON() which was introduced in MySQL 5.6.3 and MariaDB 10.0.12. This function will return the 128-bit integer corresponding to a given IPv6 address, or the 32-bit integer for an IPv4 address. A 128-bit integer equates to sixteen 8-bit bytes and thus can be stored in a BINARY(16) column. If you're storing IPv4 as well, a VARBINARY(16) column will use less storage space until IPv6 becomes predominant.

To convert an IPv6 address if you are running an older database version, use PHP's inet_pton() function, which works identically, taking either an IPv4 or an IPv6 address and returning the corresponding 32-bit or 128-bit integer as a string.

To get these columns in a human-readable form, use MySQL's INET6_NTOA() or PHP's inet_ntop().

Converting an UNSIGNED column to VARBINARY

To convert an existing column holding 32-bit IPv4 addresses as an UNSIGNED INTEGER type, first change to VARBINARY: If you look at the contents of the altered column, while it may at first appear to hold the correctly converted old value, attempting to display it with INET6_NTOA() will fail: What's actually happened is that MySQL / MariaDB has converted the old integer values into their string representation and stored this ASCII string in the VARBINARY column. So we need to convert the stringified numbers into the actual number they represent: We can now get the human-readable IP addresses (also note the raw BINARY ip column is no longer readable):