Member Avatar for silviud

hi,

i have the following table:

+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| IpAddressesID | int(11) | NO | PRI | NULL | auto_increment |
| PhysicalServerID | int(11) | NO | | NULL | |
| VmID | int(11) | NO | | NULL | |
| IpNetworkID | int(11) | NO | | NULL | |
| IpAddressDigit | varchar(45) | NO | | NULL | |
+------------------+-------------+------+-----+---------+----------------+

If I have into the table the following for IpAddressDigit:

row1 - 9
row2 - 10
row3 - 15

For the next select I want to have 11 as the next digit available not 16.

For now I import all data into php and do computations but It can be done by mysql.

thanks

  • 2 Contributors
  • 3 Replies
  • 126 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by silviud

Dani AI

Generated

Short note: ’s left-join + MIN approach is the simplest and usually the fastest for “first missing successor” queries; reports that it solved the problem in practice. The user-variable / row-number method produced 1 because it implicitly compares an incrementing row counter (starting at 1) to the stored values. If the stored sequence does not begin at 1, the very first comparison will fail and you get 1 back. Another common gotcha is that storing numbers as text sorts lexicographically (so 10 can sort before 9), which breaks any ordering-based gap detection unless you cast to a numeric type.

If using MySQL 8+, a clear, robust way is to use window functions to look at consecutive rows and pick the first gap. Example (casts the column to unsigned and deduplicates first):

WITH ordered AS (
  SELECT DISTINCT CAST(IpAddressDigit AS UNSIGNED) AS ip
  FROM iptable
)
SELECT prev + 1 AS missing
FROM (
  SELECT ip, LAG(ip) OVER (ORDER BY ip) AS prev
  FROM ordered
) t
WHERE prev IS NOT NULL AND ip - prev > 1
ORDER BY prev
LIMIT 1;

Before switching column types, check for non-numeric values and duplicates:

SELECT IpAddressDigit FROM iptable
WHERE IpAddressDigit NOT REGEXP '^[0-9]+$' LIMIT 10;

If the column is meant to hold numeric identifiers, convert it to an integer (backup first) and add an index to avoid repeated CASTs. If no gap is found, return MAX(ip)+1 as a fallback. For actual IP addresses, consider storing IPv4 as integers with INET_ATON/INET_NTOA or using native binary storage for IPv6; that simplifies range and gap queries and improves performance (see MySQL window functions docs for the LAG() approach: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html).

Recommended Answers

All 3 Replies

Member Avatar for urtrivedi 
select min(a.IpAddressDigit +1) free_ip 
from iptable a 
left outer join iptable b on b.IpAddressDigit =a.IpAddressDigit +1
where   b.IpAddressDigit  is null
order by a.IpAddressDigit
Member Avatar for urtrivedi

another alternative for mysql

select min(row) from (
SELECT @row := @row + 1 as row,t.IpAddressDigit
FROM iptable t, (SELECT @row := 0) r
order by t.IpAddressDigit 
)a where a.row<>a.IpAddressDigit ;
Member Avatar for silviud

thanks for answers - the first one works fine.
the second returns

+----------+
| min(row) |
+----------+
| 1 |
+----------+

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.