How to interpret binary data as integer in mysql

Question

I'm saving ip addresses as strings using php's inet_pton as a four byte string. To create a human readable representation using mysql I want to use inet_ntoa^MySQL, however, it appears to only accept integer values.

So, how do I read a string like !"# (corresponding to ip 32.33.34.35, also notice the space) as an integer (corresponding to 539042339 in this example)?

Why are you storing it as a four byte string instead of in a four byte int field as recommended? — Umbrella
– Umbrella, Commented Jul 12, 2012 at 18:04
I deleted my answer, I don't get it, can you display more what you have and what do you expect? — jcho360
– jcho360, Commented Jul 12, 2012 at 18:20
@Umbrella Because mysql 5.6 will use varbinary(16) to store ip addresses. I want to make to make the transition easy. — McEnroe
– McEnroe, Commented Jul 12, 2012 at 19:55
@jcho360 In case of a ipv4 address what happens is that each byte is interpreted as a char, and concatenated as a string. I want to go from string to integer (eg. 255.255.255.255 -> string -> 4294967295 -> inet_ntoa()). — McEnroe
– McEnroe, Commented Jul 12, 2012 at 19:58
@McEnroe I got that, but why did you say how do I read a string like !"#? — jcho360
– jcho360, Commented Jul 12, 2012 at 20:04

ESG · Accepted Answer · 2012-07-12 20:37:14Z

2

You can use something Hex() to convert the string to hexadecimal, then Conv() to convert it to base 10.

SELECT inet_ntoa(conv(hex(' !"#'), 16, 10))

answered Jul 12, 2012 at 20:37

ESG

9,4754 gold badges41 silver badges57 bronze badges

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

Comments

Marcus Adams · Accepted Answer · 2012-07-12 20:28:36Z

1

With MySQL < 5.6.3, you can do this with a combination of SUBSTR() and ORD():

SELECT INET_NTOA(
    ORD(SUBSTR(' !"#', 1, 1)) * 16777216 +
    ORD(SUBSTR(' !"#', 2, 1)) * 65536 +
    ORD(SUBSTR(' !"#', 3, 1)) * 256 +
    ORD(SUBSTR(' !"#', 4, 1)))

Which results in:

32.33.34.35

Also, since you're practically there already, you could just create the string yourself with CONCAT():

SELECT CONCAT(
    ORD(SUBSTR(' !"#', 1, 1)), '.',
    ORD(SUBSTR(' !"#', 2, 1)), '.',
    ORD(SUBSTR(' !"#', 3, 1)), '.',
    ORD(SUBSTR(' !"#', 4, 1)))

I hope you're using VARCHAR (as opposed to CHAR) with MySQL 5+ or a binary column so you don't lose your trailing spaces.

To support IPv6, you'll need to check the length first with an IF statement and concatenate a little differently.

With MySQL 5.6.3, the new INET6_NTOA() function supports this directly. It takes in a binary string and supports both IPv4 and IPv6:

SELECT INET6_NTOA(' !"#')

edited Jul 12, 2012 at 20:28

answered Jul 12, 2012 at 19:03

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

3 Comments

McEnroe Over a year ago

I'm using varbinary. Would that a problem? Also, I will be storing ipv6 addresses in there. Is there a way to convert both/either to integers without ifs?

Marcus Adams Over a year ago

@McEnroe, VARBINARY is a binary column, so you'll be fine with trailing spaces. It seems like MySQL's INET_NTOA() function only supports IPv4, so you may as well just create the string yourself. In the above solution, it might have been easier just to concatenate the ordinal values with '.' rather than use INET_NTOA(). I updated my answer to show that.

Marcus Adams Over a year ago

@McEnroe, I updated my answer regarding MySQL 5.6.3's new INET6_NTOA() function, which supports this perfectly.

Collectives™ on Stack Overflow

How to interpret binary data as integer in mysql

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related