Home >Database >Mysql Tutorial >How to Find Missing Sequential Numbers in a MySQL Auto-Increment Column?

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

Barbara Streisand
Barbara StreisandOriginal
2025-01-12 09:15:43617browse

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:

<code class="language-sql">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</code>

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn