怎么在SQL中对IP地址段进行分类并分组统计流量？-SQL-PHP中文网

需将ip地址转为整数并用掩码运算归一化网段，ipv4用inet_aton()与掩码按位与，ipv6需单独提取前缀，禁用字符串截取以防错误。

怎么在sql中对ip地址段进行分类并分组统计流量？

用 CIDR 表示法把 IP 转成网段前缀

直接对 ip_address 字符串做 GROUP BY 没意义，得先归一化到统一粒度的网段。常见做法是转成 /24（IPv4）或 /64（IPv6），但关键在「怎么算」——不能靠字符串截取，得用整数运算防越界和掩码错误。

以 IPv4 为例，核心是把点分十进制转成 32 位整数，再按掩码与运算：

SELECT 
  INET_ATON(ip_address) & 0xFFFFFF00 AS network_int,
  CONCAT(INET_NTOA(INET_ATON(ip_address) & 0xFFFFFF00), '/24') AS cidr_24,
  SUM(traffic_bytes) AS total_bytes
FROM logs
GROUP BY network_int;

注意：0xFFFFFF00 是 /24 掩码（255.255.255.0），不是字符串拼接出来的；INET_ATON() 对非法 IP 返回 NULL，会导致该行被排除，务必提前清洗或加 WHERE ip_address REGEXP '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' 过滤。

处理 IPv6 和混合地址的兼容方案

MySQL 5.6+ 支持 INET6_ATON()，但它返回的是 VARBINARY(16)，无法直接位运算。想统一处理双栈日志，最稳的方式是：只对 IPv4 用整数掩码，IPv6 单独走前缀提取逻辑。

用 IS_IPV4(ip_address) 和 IS_IPV6(ip_address) 分流
IPv6 的 /64 网段提取：用 LEFT(INET6_NTOA(INET6_ATON(ip_address)), 19)（因为标准 IPv6 地址格式为 xxxxxxxx:xxxxxxxx:xxxxxxxx:xxxxxxxx，前 64 位占 19 个字符，含冒号）
别用 SUBSTRING_INDEX() 截 IPv6，不同压缩写法（如 ::1）会导致偏移错乱

避免 GROUP BY 时精度丢失的陷阱

有人会用 LEFT(ip_address, 8) 或正则提取前三段，这在边界上会出错：比如 192.168.99.255 和 192.168.100.1 都被截成 192.168.，但实际属于不同 /24 网段。

必须坚持用整数转换 + 掩码，且注意类型溢出：

MarsCode

MarsCode 是一款带有 AI 编程助手和云开发环境的 AI IDE 工具。

下载

INET_ATON('255.255.255.255') 返回 4294967295（无符号 32 位最大值），确保字段或计算列定义为 UNSIGNED INT
如果表里存的是字符串型 IP，又没索引，INET_ATON() 会在每行都执行，性能差；建议新增生成列 ip_int UNSIGNED INT AS (INET_ATON(ip_address)) STORED 并建索引