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 |
+-----------+---------+
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.