2

I am storing IPv6 addresses as a varbinary with PHP function inet_pton($_SERVER['REMOTE_ADDR']). When I run an SQL query such as:

SELECT ip_address FROM some_table WHERE id='some_id';

I wouldn't be able to read the ip_address because it is in a binary format.

I notice that there is a corresponding MySQL function INET6_NTOA(expr) in MySQL version 5.6 to revert it back to readable format. Unfortunately, I am using MySQL version 5.5, which doesn't have that function.

Is there any other way I can read the IPv6 addresses without going back to PHP to do the conversion? I can easily read off the hexadecimal notation of the binary string with the editor in the Workbench as shown in the image attached, so I thought there should be an easy way to do this.

enter image description here

Question Overflow
  • 10,925
  • 18
  • 72
  • 110
  • read similar : http://stackoverflow.com/questions/1873085/how-to-convert-from-varbinary-to-char-varchar-in-mysql – xkeshav Apr 06 '12 at 05:53
  • also see : http://stackoverflow.com/questions/973923/mysql-varbinary-vs-varchar and http://stackoverflow.com/questions/420680/how-to-store-ipv6-compatible-address-in-a-relational-database – xkeshav Apr 06 '12 at 05:56
  • @diEcho, I have tried `cast` and `convert`, but it didn't work. – Question Overflow Apr 06 '12 at 06:04

1 Answers1

3

You can write user-defined functions to do the conversion for you and call them in the select clause. See this link for more info.

Dmitry Reznik
  • 6,812
  • 2
  • 32
  • 27
  • Thanks for the link. Is there a way to read off the hexadecimal notation that I see with "open value in editor" on the MySQL workbench? – Question Overflow Apr 06 '12 at 06:06