Home >Database >Mysql Tutorial >How to Find Missing Sequential Numbers in a MySQL Auto-Increment Column?
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!