mysql - 将数据库列从 UNSIGNED 更改为 BINARY 而无需转换为字符串

Question

我有一个在 UNSIGNED INTEGER 列中包含 IPv4 地址的表。我现在将其更改为支持 IPv6 地址，由 INET6_ATON() 返回，它需要 VARBINARY(16) 列类型。

https://mariadb.com/kb/en/mariadb/inet6_aton/

如何更改现有列以保留其内容？

ALTER TABLE hitcounts CHANGE ip ip BINARY(16);

上面的命令似乎将无符号整数值转换为等效的字符串，这不是我想要的。

演示

CREATE TEMPORARY TABLE hitcounts (ip INTEGER UNSIGNED);
INSERT INTO hitcounts VALUES (INET_ATON('78.156.181.3')),(INET_ATON('64.255.180.66')),(INET_ATON('31.126.241.81'));
SELECT * FROM hitcounts;
+------------+
| ip         |
+------------+
| 1318892803 |
| 1090499650 |
|  528413009 |
+------------+
3 rows in set (0.00 sec)

ALTER TABLE hitcounts MODIFY COLUMN ip VARBINARY(16);
SELECT * FROM hitcounts;
+------------+
| ip         |
+------------+
| 1318892803 |
| 1090499650 |
| 528413009  |
+------------+
3 rows in set (0.00 sec)

虽然转换后的 ip 值似乎已被保留，但实际上它们已被转换为 ASCII 字符串(请注意第三行的左对齐与它是真实数字时使用的右对齐相比)。尝试获取 IP 地址将失败:

SELECT INET6_NTOA(ip) FROM hitcounts;
+----------------+
| INET6_NTOA(ip) |
+----------------+
| NULL           |
| NULL           |
| NULL           |
+----------------+
3 rows in set (0.01 sec)

Answer 1

简单的解决方案是按照描述更改列，然后更新列以转换为由 INET6_ATON() 生成的二进制字符串:

UPDATE hitcounts SET ip = INET6_ATON(INET_NTOA(ip));