INET6_ATON的替代MySQL代码 [英] alternative MySQL code for INET6_ATON

问题描述

在不使用INET6_ATON/INET6_NTOA的情况下将旧的INET_ATON值转换为新的二进制INET6_ATON值

我们在表中已有数据，该字段的类型为UNSIGNED INT，其中包含使用INET_ATON()创建的IPv4数据.

但是现在我们移至查询中的INET6_ATON()，并希望直接迁移数据而不创建用于转换的其他数据库字段.

所以我将字段类型从UNSIGNED INT更改为VARBINARY(16).

新数据通过INET6_ATON()存储为二进制值.

但是我们如何转换旧数据? 我已经尝试将现有值转换为整数并将其转换为HEX.用INET6_ATON()保存时，这给了我相同的十六进制字符串.

我错过了一些步骤或字面意思吗? 更新访客SET ip = HEX(CAST(ip AS UNSIGNED))

二进制数据不相同.在这种情况下，它将另存为十六进制值.使用BIN()，CONVERT()和CAST()没有帮助.

示例数据:

unsigned int字段中的旧ip值:
2130706433(= ip2long('127.0.0.1'))

旧值，如varbinary字段中所示:
32313330373036343333(= 2130706433)

如varbinary字段中所示的新值:
7f000001(= INET6_ATON('127.0.0.1'))

我们不能直接使用INET6_ATON()/INET6_NTOA()进行转换.

我们应该使用PHP函数inet_top()和inet_pton()转换每一行的数据，还是有一种不需要这些UDF的纯SQL解决方案，因此所有行都可以立即更新?

似乎已经存在类似的问题，但是没有解决方案，注释中提到的解决方案会处理错误的数据，并且未提供一些有效的示例代码: http://dev.mysql.com/doc/refman/5.7/zh-CN/miscellaneous-functions.html#function_inet6-aton
http://dev.mysql.com/doc/refman/5.7/zh-CN/miscellaneous-functions.html#function_inet6-ntoa
http://php.net/manual/en/function.inet-pton. php
http://php.net/manual/en/function.inet-ntop. php

解决方案

我找到了解决方案.

通过UPDATE查询，我们可以从数据库中获取原始值，再次将其转换为int，对其进行十六进制和反十六进制(可以进一步缩短吗?)，然后在数据库中获得正确的二进制值.

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

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

解决方案

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

这篇关于INET6_ATON的替代MySQL代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！