Converting VARBINARY to integer or decimal representation in MySQL

Question

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?

The general way is to use hex/conv, but for ip addresses, there is an even nicer option, have a look at SQL: Binary to IP Address — Solarflare
– Solarflare, Commented Feb 2, 2018 at 9:12
That would be the cherry on the cake but column, inet_ntoa(column) gives aabbccdd , 0.0.0.0, so the question is why columns with data are seen as zero? — xenoid
– xenoid, Commented Feb 2, 2018 at 9:36
You have to still use e.g. conv first to make it a number, inet_ntoa is, as you said, just the cherry. Check the second answer (or the last code in the question (the last "edit")), it should work for your case. If you do not get the expected result for your specific data, you should add some sample data. — Solarflare
– Solarflare, Commented Feb 2, 2018 at 12:38

xenoid · Accepted Answer · 2018-05-01 20:11:36Z

4

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

answered May 1, 2018 at 20:11

xenoid

9,1424 gold badges30 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting VARBINARY to integer or decimal representation in MySQL

Edit

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related