How to Find Missing Sequential Numbers in a MySQL Auto-Increment Column?

Find consecutively numbered gaps in MySQL

In this query, the poster encountered missing values ​​in an auto-increment column of a table, although there were no duplicates. The goal of the problem is to identify and return the values ​​of missing numbers, which may not be consecutive, such as ids 71-82.

Better solution

JustPlainMJS offers an alternative that significantly improves performance.

Less efficient solution

For tables of any size, consider the following approach:

SELECT 
  (t1.id + 1) as gap_starts_at, 
  (
    SELECT MIN(t3.id) -1 
    FROM arrc_vouchers t3 
    WHERE t3.id > t1.id
  ) as gap_ends_at
FROM 
  arrc_vouchers t1
WHERE 
  NOT EXISTS (
    SELECT t2.id 
    FROM arrc_vouchers t2 
    WHERE t2.id = t1.id + 1
  )
HAVING gap_ends_at IS NOT NULL

In this query:

• gap_starts_at represents the first id in the current gap.
• gap_ends_at represents the last id in the current gap.

The above is the detailed content of How to Find Missing Sequential Numbers in a MySQL Auto-Increment Column?. For more information, please follow other related articles on the PHP Chinese website!