有没有办法直接从 SELECT 查询中将 IP 与 IP+CIDR 进行匹配？

Question

就像是

SELECT COUNT(*) AS c FROM BANS WHERE typeid=6 AND (SELECT ipaddr,cidr FROM BANS) MATCH AGAINST 'this_ip';

因此，您不必先从数据库中获取所有记录，然后再匹配它们一一。

如果c> 0 然后匹配。

BANS表：

id int auto incr PK
typeid TINYINT (1=hostname, 4=ipv4, 6=ipv6)
ipaddr BINARY(128)
cidr INT
host VARCHAR(255)

DB：

查询时已知MySQL 5 IP和IPv类型（4或6）。

例如，二进制格式的 IP 为 ::1

禁止 IP 为例如 ::1/64

Answer 1

请记住，IP 不是文本地址，而是数字 ID。 我有类似的情况（我们正在进行地理IP查找），如果您将所有IP地址存储为整数（例如，我的IP地址是192.115.22.33，因此它存储为3228767777），那么您可以查找IP通过使用右移运算符可以轻松实现。

所有这些类型的查找的缺点是您无法从索引中受益，并且每次进行查找时都必须进行全表扫描。 上述方案可以通过存储 CIDR 网络的网络 IP 地址（范围的开头）和广播地址（范围的结尾）来改进，因此例如要存储 192.168.1.0/24，您可以存储两个列：

network     broadcast
3232235776, 3232236031 

然后您可以简单地进行匹配。

SELECT count(*) FROM bans WHERE 3232235876 >= network AND 3232235876 <= broadcast

这将允许您将 CIDR 网络存储在数据库中，并利用快速数字索引快速有效地将它们与 IP 地址进行匹配。

下面讨论的注释：

MySQL 5.0 包含一个名为“索引合并相交" 可以加速此类查询（并避免全表扫描），只要：

• 存在匹配的多列索引正是查询中的列（按顺序）。 因此，对于上面的查询示例，索引需要为(network,broadcast)。
• 所有数据都可以从索引中检索。 对于 COUNT(*) 是这样，但对于 SELECT * ... LIMIT 1 则不然。

MySQL 5.6 包含一个称为 MRR 的优化，它也可以加速全行检索，但这超出了本答案的范围。

Answer 2

IPv4 地址、网络地址和网络掩码都是 UINT32 数字，并以人类可读的形式呈现为“点分四组”。 在检查地址是否位于给定网络空间（网络/网络掩码）中时，内核中的路由表代码执行非常快速的按位 AND 比较。 这里的技巧是将点分四组的 IP 地址、网络地址和网络掩码存储在表中作为 UINT32，然后执行相同的 32 位按位 AND 进行匹配。 例如

SET @test_addr = inet_aton('1.2.3.4');
SET @network_one = inet_aton('1.2.3.0');
SET @network_two = inet_aton('4.5.6.0');
SET @network_netmask = inet_aton('255.255.255.0');

SELECT (@test_addr & @network_netmask) = @network_one AS IS_MATCHED;
+------------+
| IS_MATCHED |
+------------+
|          1 |
+------------+

SELECT (@test_addr & @network_netmask) = @network_two AS IS_NOT_MATCHED;
+----------------+
| IS_NOT_MATCHED |
+----------------+
|              0 |
+----------------+

Answer 3

对于 IPv4，您可以使用：

SET @length = 4;

SELECT  INET_NTOA(ipaddr), INET_NTOA(searchaddr), INET_NTOA(mask)
FROM  (
  SELECT
        (1 << (@length * 8)) - 1 & ~((1 << (@length * 8 - cidr)) - 1) AS mask,
        CAST(CONV(SUBSTR(HEX(ipaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS ipaddr,
        CAST(CONV(SUBSTR(HEX(@myaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS searchaddr
  FROM  ip
) ipo
WHERE ipaddr & mask = searchaddr & mask

Answer 4

生成整数 IP 地址范围

如果您的数据库不支持奇特的按位运算，您可以使用基于简化整数的方法。

以下示例使用 PostgreSQL：

select (cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 1) as bigint) * (256 * 256 * 256) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 2) as bigint) * (256 * 256      ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 3) as bigint) * (256            ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 4) as bigint)) 
        as network,

       (cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 1) as bigint) * (256 * 256 * 256) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 2) as bigint) * (256 * 256      ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 3) as bigint) * (256            ) +
        cast(split_part(split_part('4.0.0.0/8', '/', 1), '.', 4) as bigint)) + cast(
          pow(256, (32 - cast(split_part('4.0.0.0/8', '/', 2) as bigint)) / 8) - 1 as bigint
        ) as broadcast;

Answer 5

嗯。 您可以构建一个 cidr 掩码表，加入它，然后将 ip 和（MySQL 中的 &）与带有禁止块 ip 地址的掩码进行比较。 那会达到你想要的效果吗？

如果您不想构建掩码表，可以将掩码计算为 -1 << (x-cidr) ，具体取决于 x = 64 或 32。

Answer 6

#my subnet 
SET @subnet = '192.168.1.0';
SET @mask = '255.255.254.0';
SET @cidr = 23;


#check 192.168.0.4 is in my subnet

#use mask
SELECT INET_ATON('192.168.0.4') & INET_ATON(@mask) = INET_ATON(@subnet) & INET_ATON(@mask) AS in_subnet;

#use cidr
SELECT INET_ATON('192.168.0.4') & -1 << 32 - @cidr = INET_ATON(@subnet) & -1 << 32 - @cidr AS in_subnet;