Daniel Ruf Daniel Ruf - 11 days ago 5
SQL Question

alternative MySQL code for INET6_ATON

Convert old INET_ATON value to new binary INET6_ATON value without INET6_ATON / INET6_NTOA

We have existing data in a table, the field is of type

UNSIGNED INT
which holds IPv4 data which was created with
INET_ATON()
.

But now we move to
INET6_ATON()
in the queries and want to migrate the data diectly without creating additional database fields for the conversion.

So I changed the field type from
UNSIGNED INT
to
VARBINARY(16)
.

New data is stored as binary value with
INET6_ATON()
.

But how can we convert the old data?
I already tried to cast the existing values to integer and convert them to with
HEX
. This gives me the same hex string when saving it with
INET6_ATON()
.

Did I miss some step or some literal?
UPDATE visitors SET ip = HEX(CAST(ip AS UNSIGNED))

The binary data is not the same. In this case it is saved as hex value. Using
BIN()
,
CONVERT()
and
CAST()
did not help.

Example data:

old ip value in unsigned int field:

2130706433 ( = ip2long('127.0.0.1') )

old value as shown in varbinary field:

32313330373036343333 ( = 2130706433 )

new value as shown in varbinary field:

7f000001 ( = INET6_ATON('127.0.0.1') )

We can not directly use
INET6_ATON()
/
INET6_NTOA()
for conversion.

Should we convert the data for every row with the PHP functions
inet_top()
and
inet_pton()
or is there a pure SQL solution for this without the need for some UDF so all rows are updated at once?

It seems there was already a similar question but there is no solution and the solution mentioned in the comments procudes wrong data and does not provide some working example code: Convert IPv6 to binary without INET6_ATON()

References:

http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet6-aton

http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet6-ntoa

http://php.net/manual/en/function.inet-pton.php

http://php.net/manual/en/function.inet-ntop.php

Answer

I found the solution.

With an UPDATE query we can fetch the original value from the database, cast it to int again, hex and unhex it (can this be further shortened?) and we get the right binary value in the database.

UPDATE table SET ip = UNHEX(HEX(CAST(ip AS UNSIGNED)))

Comments