Search code examples
mysqlphpmyadminvarbinary

Converting VARBINARY to integer or decimal representation in MySQL

I have a MySQL (5.7.21) application database that contains IP (v4) addresses as VARBINARY(16) (I assume this length is meant to support IPv6).

I typically access this DB for maintenance purposes through phpMyAdmin.

I need to extract these IP addresses to some text format that I can handle with a script. PhpMyAdmin displays these columns in hex in query results, but exporting to file creates files that are difficult to handle in scripts or with a text editor.

I tried several variants of CAST/CONVERT but nothing seems to work (in particular casts to SIGNED or UNSIGNED yield 0).

Is there a solution? Am I overlooking something?

Edit

OK, so a solution is using: 

INET_NTOA ( CONV ( HEX (varbinary_column),16,10 ) )

But it the bin-to-hex-to-decimal really necessary or is there a shorter form?

Solution

  • OK, so the real secret is to use INET6_NTOA(varbinary_column) (note the 6) (works also with IP v4 addresses). Couldn't be easier...