mysql - 如何从mysql数据库表中选择可用的IP地址

Question

我有一个名为 ip_address 的 mysql 表，其中包含名为 ip 的列，它包含以下数据

192.168.1.52192.168.1.7192.168.1.21192.168.1.107. . .

my problem is how to get the available IP address from the subnet, I mean this subnet must contain the range from 192.168.1.1 to 192.168.1.255 but not all IPs were stored, so i managed to sort them by:

SELECT ip FROM ip_address ORDER BY INET_ATON(ip)

我想检索该范围内 ip_address 表中未找到的所有 IP。

Answer 1

假设您可以创建一个名为 numbers 的表，其中(至少)包含 1 到 255 之间的所有整数，那么您可以使用如下查询来查找给定子网未使用的 IP 地址:

SELECT CONCAT('192.168.1.',numbers.n) as available_ip
FROM numbers
LEFT OUTER JOIN ip_address ON ip_address.ip = concat('192.168.1.',numbers.n)
WHERE numbers.n BETWEEN 1 AND 255
  AND ip_address.ip IS NULL
ORDER BY numbers.n;