MySQL BETWEEN函数返回非范围结果，IP匹配查询异常求助

问题原因 #

你遇到的错误是因为IP分段字段的类型是字符串而非整数，导致MySQL按字典序而非数值大小进行比较。比如你的示例中，Prague的ip_end_4是字符串'7'，当比较'106' BETWEEN '0' AND '7'时，字典序里'106'的首字符'1'小于'7'，会被判定为满足条件，从而错误返回该记录。

解决方案 #

方案1：修正字段类型（推荐）

将ip_start_1、ip_start_2、ip_start_3、ip_start_4以及对应的ip_end_x字段全部改为INT UNSIGNED类型。修改后直接使用数值比较即可，无需额外转换：

SELECT xd.city, ip_start, ip_end
FROM customIpAndCity as xd
WHERE xd.ip_start_1 = 2 
  AND 114 BETWEEN ip_start_2 AND ip_end_2
  AND 144 BETWEEN ip_start_3 AND ip_end_3
  AND 106 BETWEEN ip_start_4 AND ip_end_4;

方案2：查询时转换字段类型（无需修改表结构）

如果无法修改表结构，在查询时用CAST()或CONVERT()将字符串字段转为无符号整数，确保按数值比较：

SELECT xd.city, ip_start, ip_end
FROM customIpAndCity as xd
WHERE xd.ip_start_1 = 2 
  AND 114 BETWEEN CAST(ip_start_2 AS UNSIGNED) AND CAST(ip_end_2 AS UNSIGNED)
  AND 144 BETWEEN CAST(ip_start_3 AS UNSIGNED) AND CAST(ip_end_3 AS UNSIGNED)
  AND 106 BETWEEN CAST(ip_start_4 AS UNSIGNED) AND CAST(ip_end_4 AS UNSIGNED);

方案3：将IP转为整数存储（最优解）

放弃分段存储的方式，直接将整个IP地址转为整数存储：

1. 新增两个INT UNSIGNED类型字段：ip_start_num和ip_end_num
2. 用INET_ATON()函数批量更新现有数据：

   UPDATE customIpAndCity 
   SET ip_start_num = INET_ATON(ip_start), 
       ip_end_num = INET_ATON(ip_end);
   

3. 查询时只需将目标IP转为整数后直接比较范围：

   SELECT city, ip_start, ip_end
   FROM customIpAndCity
   WHERE INET_ATON('2.114.144.106') BETWEEN ip_start_num AND ip_end_num;
   

这种方式逻辑更简洁，还能给ip_start_num和ip_end_num添加索引，大幅提升查询性能。

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