如何高效检查数据库中是否存在用户指定的数值范围？

Alright, let's tackle this problem. You want to efficiently verify if a new numeric range (new_start, new_end) can be added to your numbers table without overlapping any existing [Starting Number, Ending Number] pairs. As your example shows, valid ranges must fit entirely in gaps before the smallest existing range, after the largest existing range, or between two consecutive non-overlapping ranges.

Core Logic: Detecting Overlapping Ranges #

The key insight here is using a universal interval-overlap check. Two ranges [a1, b1] and [a2, b2] overlap if and only if:
a1 <= b2 AND a2 <= b1

This single condition covers all possible overlap scenarios:

• The new range starts inside an existing range
• The new range ends inside an existing range
• The new range fully contains an existing range
• An existing range fully contains the new range

Efficient SQL Query #

Use this query to check for conflicts. It returns TRUE if any overlapping range exists (meaning you can't add the new range), and FALSE if it's safe to insert:

SELECT EXISTS (
    SELECT 1
    FROM numbers
    WHERE new_start <= ending_number AND starting_number <= new_end
);

How It Works with Your Example

• If existing range is 102-122 and new range is 123-150:
  123 <= 122 is false → no overlap detected → returns FALSE (valid)
• If new range is 50-101:
  102 <= 101 is false → no overlap detected → returns FALSE (valid)
• If new range is 110-130:
  110 <= 122 and 102 <= 130 are both true → overlap detected → returns TRUE (invalid)

Performance Optimization #

To make this query run quickly even on large tables, add a composite index on the two range columns:

CREATE INDEX idx_numbers_start_end ON numbers (starting_number, ending_number);

This index allows the database to quickly narrow down potential overlapping ranges without scanning the entire table. The EXISTS clause also ensures the query stops as soon as it finds the first overlapping range, which saves additional time.

Edge Cases to Consider #

• Adjacent ranges: If your new range ends at existing_start - 1 (e.g., 1-101 next to 102-122) or starts at existing_end + 1 (e.g., 123-150 next to 102-122), the query will correctly return FALSE (no overlap, valid to insert).
• Empty table: If the numbers table has no records, the query returns FALSE, so you can safely add the first range.

内容的提问来源于stack exchange，提问作者Joe