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
which holds IPv4 data which was created with
But now we move to
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
New data is stored as binary value with
But how can we convert the old data?
I already tried to cast the existing values to integer and convert them to with
. This gives me the same hex string when saving it with
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
did not help.
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
Should we convert the data for every row with the PHP functions
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()