MySQL LEAST() 具有任意数量的参数；表中最长的匹配

Question

我想创建一个 MySQL 查询来查找子网表中存在的最长匹配(给定 IP 地址的四点格式)。

最终，我想创建一个 LEFT JOIN这将显示一个表中的每个四点 IP 地址及其在另一个表中的最长匹配项。我不想创建任何临时表或将其构建为嵌套查询。

我是一个 MySQL 新手，但我的想法是这样的:

SELECT `ip_address`
  LEFT JOIN ON 
    SELECT `subnet_id`
    FROM `subnets_table`
    WHERE (`maximum_ip_value` - `minimum_ip_value`) =
    LEAST(<list of subnet intervals>)
      WHERE INET_ATON(<given ip address>) > `minimum_ip_value`
      AND INET_ATON(<given ip address>) < `maximum_ip_value`;

这样minimum_ip_value和maximum_ip_value是给定子网中可能的最低和最高十进制格式的 IP 地址 - 例如，对于子网 172.16.0.0/16:

minimum_ip_value = 2886729728 (or 172.16.0.0)
maximum_ip_value = 2886795263 (or 172.16.255.255)

和<list of subnet intervals>包含 subnets_table 中的所有区间哪里<given ip address>介于minimum_ip_value之间和maximum_ip_value

如果多个区间包含 <given ip address> ，然后连接最小间隔(即最小子网，或最具体且“最长”的匹配)。

最终，我真正想要的是 subnet_id与该间隔对应的值。

所以我的问题是:

1) 我可以使用带有任意数量参数的 LEAST() 函数吗？我想比较 subnets_table 的每一行，或更具体地说，每行之间的间隔 minimum_ip_value和maximum_ip_value ，然后选择最小的间隔。

2) 我可以在 LEFT JOIN 内执行所有这些计算吗？询问？我同意任何快速、封装并避免重复查询相同数据的建议。

我想知道这是否可以在单个查询中执行(即，无需查询每个 IP 地址的子网表)，但我不知道是否有足够的信息来排除它。如果这看起来行不通，请告诉我，这样我可以尝试另一个角度。

谢谢。

Answer 1

经过一些研究和反复试验，我发现上面的原型(prototype)查询存在一些问题:

LEAST() 函数仅接受一定数量的参数。根据我最初的问题，我想要一个可以处理任意数量的参数或表中的每一行的函数。这是 MySQL 中的另一个函数，MIN()。

函数 MIN() 的优先级低于 MySQL 中的 JOIN 函数，并且在任何给定的 JOIN 函数之后进行计算询问。因此，我无法 JOIN 到一组值的 MIN() 上，因为 MIN() 尚不存在执行 JOIN 的时间。

我认为解决此问题的唯一方法是执行两个单独的查询:一个使用 MIN()，首先执行，另一个使用 JOIN，对第一个查询的结果执行。这意味着对于包含 n 行的表，我将执行 n^n 查询，而不是 n查询。这是 Not Acceptable 。

为了解决这个问题，我编写了一个新脚本，在执行任何这些查询之前修改数据库。每个子网都有自己的 IP 值“桶”，并且该范围内的所有值都映射到该子网。如果更具体的(即较小的)子网与不太具体的(即更大的)子网重叠，则更具体的范围仅映射到较小的子网，而较大的子网仅保留不太具体的范围中的值。现在，任何给定的 IP 地址仅属于一个“桶”，并且仅映射到一个子网，这是其最具体的匹配。我可以 JOIN 参加这场比赛，而不必担心 MIN() 函数。