MySQL:匹配重复的IPv6子网前缀

Question

我有问题找到正确的方法来正确返回(和匹配)存储在DB中的IPv6地址的第一个/64位，如:2a02:a420:0003:7dbc:0002:0001:b693:9622

我需要检索表中使用IPv6(/64位)的相同网络/子网的条目

备注:该表包括IPv4地址和IPv6地址

我正在使用MySQL Workbench 8.0

Answer 1

假设您将addrcolumn存储为具有IPv6地址的VARCHAR(39)文本字符串，并且您有@matchaddr作为包含有效地址的文本字符串，在您想要匹配的/64范围内。

您可以像这样获得addrcolumn的前16位十六进制数字:

SUBSTRING(HEX(INET6_ATON(addrcolumn)),1,16)

你可以像这样用LIKE运算符进行匹配

WHERE      HEX(INET6_ATON(addrcolumn)) 
LIKE CONCAT(SUBSTRING(HEX(INET6_ATON(@matchaddr)),1,16), '%')

在字符串操作函数之后，它看起来像

WHERE `2a02a42000037dbc00020001b693962a' LIKE '2a02a42000037dbc%'

需要INET6_ATON()，因为IPv6地址通常用两个冒号缩短，意思是像这样的一堆零:

2a02:a420::2:1:b693:9622

INET6_ATON()可以正确处理。

编辑但是，如果你假设你的ipv6地址的表示从来没有，从来，包含任何缩短通过:或:1:2:风格的缩写，你可以只是做子字符串匹配与LIKE。

WHERE addrcolumn LIKE CONCAT(SUBSTRING(@matchaddr)),1,19), '%')

注意19个字符的子字符串。它看起来像

WHERE `2a02:a420:0003:7dbc:0002:0001:b693:962a'
LIKE '2a02:a420:0003:7dbc%'

注意:真正的用户会违背你的假设。

在MySQL 8+中，您可以将二进制表示转换为非缩写文本表示，如下所示:

SELECT ​LOWER(
​SUBSTRING(
​REGEXP_REPLACE(HEX(binaryAddr), '([0-9a-f]{4})', '$1:'), 1, 39))

在MariaDB 10+中是:

SELECT ​LOWER(
​SUBSTRING(
​REGEXP_REPLACE(HEX(binaryAddr), '([0-9a-f]{4})', '\1:'), 1, 39))

有一天MariaDB或MySQL团队会添加一些ipv6特定的索引/过滤的东西吗?如果他们这样做了就太好了。