这已经出现在我的一个相关项目中，这似乎是问题的最佳谷歌结果，所以，你得到答案！
create function get_lowest_ipv4(cidr char(18)) returns bigint deterministic return INET_ATON(SUBSTRING_INDEX(cidr, '/', 1));
create function get_highest_ipv4(cidr char(18)) returns bigint deterministic return get_lowest_ipv4(cidr) + (0x100000000 >> SUBSTRING_INDEX(cidr,'/', -1)) - 1;
然后你可以做... from ip_map where INET_ATON("ip.add.re.ss") between get_lowest_ipv4(ip) AND get_highest_ipv4(ip)
因为您将函数声明为确定性函数，所以它将被缓存在mysql中，并且计算只需要运行一次 . 那么它只是'是大于y且小于x'的整数，这将是有效的瞬间 .
MySQL [astpp]> set @cidr="10.11.0.0/16";
Query OK, 0 rows affected (0.00 sec)
MySQL [astpp]> select get_lowest_ipv4(@cidr), get_highest_ipv4(@cidr), INET_NTOA(get_lowest_ipv4(@cidr)), INET_NTOA(get_highest_ipv4(@cidr));
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| get_lowest_ipv4(@cidr) | get_highest_ipv4(@cidr) | INET_NTOA(get_lowest_ipv4(@cidr)) | INET_NTOA(get_highest_ipv4(@cidr)) |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| 168493056 | 168558591 | 10.11.0.0 | 10.11.255.255 |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
1 row in set (0.00 sec)
MySQL [astpp]> set @cidr="10.11.12.1/32";
Query OK, 0 rows affected (0.00 sec)
MySQL [astpp]> select get_lowest_ipv4(@cidr), get_highest_ipv4(@cidr), INET_NTOA(get_lowest_ipv4(@cidr)), INET_NTOA(get_highest_ipv4(@cidr));
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| get_lowest_ipv4(@cidr) | get_highest_ipv4(@cidr) | INET_NTOA(get_lowest_ipv4(@cidr)) | INET_NTOA(get_highest_ipv4(@cidr)) |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| 168496129 | 168496129 | 10.11.12.1 | 10.11.12.1 |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
1 row in set (0.01 sec)
MySQL [astpp]>
唯一重要的警告是您插入VALID CIDR . 例如，10.11.12.13/24无效 . 那是10.11.12.0/24网络中的IP地址 .
如果你在插入它们之前无法验证CIDR(出于某种疯狂的原因)，你可以改变get_lowest_ipv4来对源进行逐位比较，但这不那么优雅 .
INET_ATON(SUBSTRING_INDEX(`ip`, '/', 1)) & 0xffffffff ^((0x1 <
(未经测试)是否匹配无效的CIDR .