How to Convert a Binary IP Address to a String in MySQL?

To output binary IP addresses (e.g. stored in a VARBINARY column) as a string, you can use the MySQL INET6_NTOA() function.

For example, let's suppose you have the following MySQL table with VARBINARY column for IP address storage:

+----------------------------------------+---------+
| ip                                     | comment |
+----------------------------------------+---------+
| 127.0.0.1                              |  ip v4  |
| 2001:db8:3333:4444:cccc:dddd:eeee:ffff |  ip v6  |
+----------------------------------------+---------+

This can be created using the following code:

CREATE TABLE `event_log` (
    `ip` VARBINARY(16) NOT NULL,
    `comment` VARCHAR(150) NOT NULL
);

INSERT INTO `event_log` (`ip`, `comment`)
VALUES
    (INET6_ATON('127.0.0.1'), 'ip v4'),
    (INET6_ATON('2001:db8:3333:4444:CCCC:DDDD:EEEE:FFFF'), 'ip v6')
;

Using INET6_NTOA(), you can output IP addresses as a string in the following way:

-- MySQL 5.6.3+
SELECT INET6_NTOA(ip) AS `ip` FROM `event_log`;

This would produce the following output:

+----------------------------------------+
| ip                                     |
+----------------------------------------+
| 127.0.0.1                              |
| 2001:db8:3333:4444:cccc:dddd:eeee:ffff |
+----------------------------------------+

Similarly, to compare an IP address in the WHERE clause, you can do either of the following:

-- compare against string ip address
SELECT * FROM `event_log` WHERE INET6_NTOA(ip) = '127.0.0.1'

-- compare against hexadecimal ip address
SELECT * FROM `event_log` WHERE HEX(ip) = '7f000001'

Using either of these would produce the following output:

+-----------+---------+
| ip        | comment |
+-----------+---------+
| 127.0.0.1 |  ip v4  |
+-----------+---------+