Home >Database >Mysql Tutorial >How Can I Find Missing IDs in a MySQL Table?
Identifying Missing Identifiers in a MySQL Table
Within a MySQL table, it's possible to encounter missing identifiers, creating gaps in the numerical sequence. To retrieve these missing IDs, a specific query is required.
Query for Missing IDs
Consider the example table provided:
ID | Name |
---|---|
1 | Bob |
4 | Adam |
6 | Someguy |
From this table, it's evident that the IDs 2, 3, and 5 are missing. To extract these missing identifiers, the following query can be employed:
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end FROM testtable AS a, testtable AS b WHERE a.id < b.id GROUP BY a.id HAVING start < MIN(b.id);
Explanation
Additional Resources
For further insight on this topic, refer to the following:
The above is the detailed content of How Can I Find Missing IDs in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!