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.