SQL如何逐行校验子查询 实现IP地址内外网属性判断

基础信息 #

现有存储内网IP段的表 PrivateIPAddress，共包含3个字段：

• id：记录主键
• ipAddress：网段起始IP
• ipAddressTo：网段结束IP

表内预置的标准私网IP段数据如下：

需求逻辑 #

输入任意IP地址，若该IP落在表中任意一条记录的ipAddress与ipAddressTo区间范围内，即为内网IP，否则为公网IP。例如IP 10.70.10.1 匹配第一条记录的网段，属于内网IP。

原有写法问题 #

之前尝试的SQL如下，执行结果不符合预期：

SELECT (INET_ATON('10.70.10.1') >= ANY (select inet_aton(ipAddress) from PrivateIPAddress) OR INET_ATON('10.70.10.1') <= ANY (select inet_aton(ipAddressTo) from PrivateIPAddress));

错误根因：该写法将所有网段的起始IP、结束IP拆成了两个独立的数值集合做匹配，没有逐行绑定同一条记录的起止IP边界，无法实现单个网段的成对范围校验。
已知INET_ATON()函数可将IP地址转换为无符号整数，支持直接通过数值大小比较判断IP所属范围。

正确SQL写法 #

核心逻辑：逐行校验输入IP的整数值，是否同时满足「≥ 当前行起始IP整数值」且「≤ 当前行结束IP整数值」，只要任意一条网段记录匹配，即可判定为内网IP。

推荐写法：EXISTS逐行匹配（性能最优） #

该写法在匹配到第一条符合条件的网段后就会终止遍历，大表场景下性能最好，返回值1代表内网IP，0代表公网IP：

-- 替换单引号内的IP为实际需要判断的地址即可
SELECT EXISTS (
    SELECT 1
    FROM PrivateIPAddress
    WHERE INET_ATON('待判断的IP地址') BETWEEN INET_ATON(ipAddress) AND INET_ATON(ipAddressTo)
) AS is_private_ip;

备选写法：COUNT聚合判断 #

逻辑和上述写法一致，通过统计匹配的网段数量判断是否为内网：

SELECT COUNT(1) > 0 AS is_private_ip
FROM PrivateIPAddress
WHERE INET_ATON('待判断的IP地址') >= INET_ATON(ipAddress)
  AND INET_ATON('待判断的IP地址') <= INET_ATON(ipAddressTo);

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