MySQL 8.0 如何匹配并查询IPv6地址的相同/64子网前缀条目

核心逻辑：IPv6地址总长度为128位，/64前缀即前64位（对应8字节），只需对比IPv6地址二进制格式的前8字节即可判断是否属于同一子网，且可通过二进制长度区分IPv4和IPv6（IPv4转二进制后固定为4字节，IPv6为16字节）。

直接查询实现（无额外表结构修改） #

假设存储IP的表名为ip_table，IP地址字段名为ip_addr，存储格式为明文字符串：

SELECT * FROM ip_table
WHERE
  -- 过滤仅保留IPv6地址
  LENGTH(`INET6_ATON`(ip_addr)) = 16
  AND
  -- 匹配目标/64子网
  LEFT(`INET6_ATON`(ip_addr), 8) = LEFT(`INET6_ATON`('2a02:a420:0003:7dbc:0002:0001:b693:9622'), 8);

高频查询优化方案 #

如果需要频繁执行该类查询，可新增生成列存储前缀并建索引，大幅提升查询效率：

1. 新增生成列并建索引

ALTER TABLE ip_table
ADD COLUMN ipv6_64_prefix BINARY(8) GENERATED ALWAYS AS (
  IF(LENGTH(`INET6_ATON`(ip_addr))=16, LEFT(`INET6_ATON`(ip_addr), 8), NULL)
) STORED;

CREATE INDEX idx_ipv6_64_prefix ON ip_table(ipv6_64_prefix);

2. 优化后的查询语句

SELECT * FROM ip_table
WHERE ipv6_64_prefix = LEFT(`INET6_ATON`('2a02:a420:0003:7dbc:0002:0001:b693:9622'), 8);

注意事项 #

• 如果ip_addr字段已经是通过INET6_ATON转换后的VARBINARY(16)格式存储，可跳过转换步骤直接调用LEFT(ip_addr, 8)匹配即可
• INET6_ATON会自动兼容带前导零、缩写格式的合法IPv6地址，无需提前做字符串标准化处理

内容的提问来源于stack exchange，提问作者Skildpet