What's The Best Way To Store An IP Address In MySQL?

To optimize the storage of an IP address (v4 or v6) in a MySQL database consider using VARBINARY data type for the ip storage field as it uses less storage space by storing byte strings rather than character strings. In this article we look at ways to convert a string IP address into its corresponding binary representation that is database storage-ready.

Using PHP's inet_pton() Function

This can be used for both IP v4 and v6. This function converts an IP address into 32-bit or 128-bit binary string which means setting VARBINARY to a length of 16 should be sufficient (because 128-bits equal to 16-bytes). In case you're only concerned about IP v4 addresses you could use VARBINARY(4) (because 32-bits equal to 4-bytes). Please note that FALSE is returned if an invalid IP address is supplied as an argument.

// PHP 5.1+

// convert to binary
$ip_bin = inet_pton('127.0.0.1'); // output: 7f000001 (hexadecimal)

// convert back to string
$ip = inet_ntop($ip_bin); // output: 127.0.0.1 (string)

Storage:

// using PHP PDO
$ip = '127.0.0.1';
$ip_bin = inet_pton($ip);

$sth = $dbhandle->prepare("INSERT INTO user_ip (ip) VALUES (?)");
$sth->execute(array($ip_bin));

Adding the binary characters directly into the query string may yield unexpected results, therefore, it is recommended you use PHP PDO's execute mechanism with a prepared statement and a bound binary IP value (like shown above).

Comparison:

To compare a specified ip address to the binary equivalent stored in the database, you could do the following:

// using PHP PDO
$ip = '127.0.0.1';
$ip_bin = inet_pton($ip);

$sth = $dbhandle->prepare("SELECT * FROM user_ip WHERE ip = ?");
$sth->execute(array($ip_bin));

We could also convert the stored IP address into its hexadecimal equivalent and compare it against a hexed ip:

// PHP 5.1+
$ip_hexed = bin2hex(inet_pton($ip)); // output: 7f000001 (hexadecimal)

// using SQL
SELECT * FROM user_ip WHERE HEX(ip) = '$ip_hexed'

Using MySQL's INET6_ATON() Function

This can be used for both IP v4 and v6. The binary string representation returned by this function has a max length of 16-bytes for IPv6 addresses and 4-bytes for IPv4 addresses. If the argument supplied to this function is not a valid IP address, NULLis returned.

// MySQL 5.6.3+

SELECT HEX(INET6_ATON('127.0.0.1')); // output: 7f000001 (hexadecimal)

SELECT INET6_NTOA(ip); // output: 127.0.0.1 (string)

In the first query we're using HEX to display the result in a printable form.

Storage:

INSERT INTO user_ip (ip) VALUES (INET6_ATON('127.0.0.1'))

Comparison:

SELECT * FROM user_ip WHERE INET6_NTOA(ip) = '127.0.0.1'

SELECT * FROM user_ip WHERE HEX(ip) = '7f000001'