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 |
|                              |  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`)
    (INET6_ATON(''), '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                                     |
|                              |
| 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) = ''
-- compare against hexadecimal ip address
SELECT * FROM `event_log` WHERE HEX(ip) = '7f000001'

Using either of these would produce the following output:

| ip        | comment |
| |  ip v4  |

This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.