怎样在SQL中对IP地址段进行分组归并与聚合统计-SQL-PHP中文网

正确方法是将ipv4转为整数后按掩码位运算提取前缀再分组聚合，避免substring_index或like等字符串操作导致的网段误分、排序失效及性能问题。

怎样在sql中对ip地址段进行分组归并与聚合统计

直接用字符串截取或模糊匹配 IP 段，统计结果不可靠，也难扩展。正确路径是：先转整数，再按掩码位运算提取前缀，最后分组聚合。MySQL、PostgreSQL、SQL Server 都能走通，无需扩展函数。

为什么不能用 SUBSTRING_INDEX 或 LIKE 做网段分组

看似简单，实则埋雷：SUBSTRING_INDEX(ip, '.', 3) 会把 '10.1.1.1' 和 '10.1.1.128' 分成不同组，但它们在 /24 网段下本应同属 10.1.1.0/24；更严重的是，'192.168.2.1' 字典序 > '192.168.10.1'，数值上却更小——字符串排序完全失效。

IPv4 是 32 位无符号整数，必须还原为数值语义才能做区间/掩码运算
LIKE '192.168.%' 无法表达 CIDR 掩码长度（比如 /23 和 /24 行为不同）
正则或模糊匹配无法走索引，千万级日志表一跑就卡住

用 INET_ATON + 位运算提取网段前缀（MySQL）

这是 MySQL 最稳的方案，但要注意写法细节：

掩码必须用十六进制字面量，如 0xFFFFFF00（对应 /24），写成 255.255.255.0 会报错
位运算对象必须是整数：INET_ATON(ip) & 0xFFFFFF00，结果仍是整数，聚合后可用 INET_NTOA() 转回可读格式
别在 WHERE 里对 INET_ATON(ip) 做运算——全表扫描不可避免；高频查询务必冗余存 network_24 字段并加索引
非法 IP（如 '256.1.1.1'）会让 INET_ATON() 返回 NULL，建议前置清洗：WHERE ip REGEXP '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$'

示例（按 /24 聚合）：

PathFinder

一款AI数据处理工具，主要用于AI驱动的销售漏斗分析工具，适合需要提升相关任务效率的用户。

下载

SELECT INET_NTOA(INET_ATON(ip) & 0xFFFFFF00) AS network, COUNT(*) AS cnt
FROM access_log 
WHERE ip REGEXP '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$'
GROUP BY INET_ATON(ip) & 0xFFFFFF00;

不依赖内置函数的手算 IP 转整数（跨数据库通用）

当用 PostgreSQL 或 SQL Server，或担心 INET_ATON() 兼容性时，推荐手算。本质就是 a×256³ + b×256² + c×256 + d：

MySQL 写法：(SUBSTRING_INDEX(ip, '.', 1) * 16777216 + SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1) * 65536 + SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 3), '.', -1) * 256 + SUBSTRING_INDEX(ip, '.', -1))
PostgreSQL 写法：(SPLIT_PART(ip, '.', 1)::int * 16777216 + SPLIT_PART(ip, '.', 2)::int * 65536 + SPLIT_PART(ip, '.', 3)::int * 256 + SPLIT_PART(ip, '.', 4)::int)
结果类型必须是 UNSIGNED INT 或 BIGINT，否则溢出（如 MySQL 的有符号 INT 会把 192.168.1.1 算成负数）
算完再做位运算或 BETWEEN 区间判断，逻辑和 MySQL 完全一致

按预设 IP 段做业务归类（非标准网段）

如果要归类“公司内网（10.0.0.0/8）、阿里云（100.64.0.0/10）、AWS（52.0.0.0/5）”这类混合 CIDR，硬编码位运算是灾难。正确做法是建一张映射表：

CREATE TABLE ip_range_map (
  id INT PRIMARY KEY,
  cidr_start BIGINT NOT NULL,
  cidr_end BIGINT NOT NULL,
  label VARCHAR(32) NOT NULL,
  INDEX idx_range (cidr_start, cidr_end)
);