Find missing numbers in a sequence

from the Artful Common Queries page


You have a table tbl(id int) with values (1,2,4,18,19,20,21), and you wish to find the first missing number in its sequence of id values:

SELECT t1.id+1 AS Missing
FROM tbl      AS t1
LEFT JOIN tbl AS t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY id LIMIT 1;
+---------+
| Missing |
+---------+
|       3 |
+---------+

For all the gaps, including gaps of more than 1 value, aggregate on an inequality join ...

SELECT a.id+1 AS 'Missing From', MIN(b.id)-1 AS 'Through'
FROM tbl AS a
JOIN tbl AS b ON a.id < b.id
GROUP BY a.id
HAVING a.id+1 < MIN(b.id);
+--------------+-----------+
| Missing From | Through   |
+--------------+-----------+
|            3 |         3 |
|            5 |        17 |
+--------------+-----------+

We often need such lists, so the query is a natural for a stored procedure that finds missing sequence values in any table:

DROP PROCEDURE IF EXISTS MissingInSeq;
DELIMITER |
CREATE PROCEDURE MissingInSeq( db VARCHAR(64), tbl VARCHAR(64), col VARCHAR(64) )
BEGIN
  SET @sql = CONCAT( "SELECT  a.", col, 
                     "+1 AS 'Missing From',", 
                     "MIN(b.", 
                     col, 
                     ") - 1 AS 'Through' FROM ",
                     db, 
                     ".", 
                     tbl, 
                     " AS a,", 
                     db, 
                     ".", 
                     tbl, 
                     " AS b WHERE a.", 
                     col, 
                     " < b.", 
                     col, 
                     " GROUP BY a.", 
                     col, 
                     " HAVING a.", 
                     col, 
                     " < MIN(b.", 
                     col, 
                     ") - 1" 
                   ); 
  -- SELECT @sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DROP PREPARE stmt;
END;
|
DELIMITER ;

Auto_increment doesn't guarantee sequentiality, but sometimes—e.g., when you're initialising a production lookup table with predefined values—it's necessary to tighten up a table's auto_increment sequence. A simple method assuming table tbl with id int unsigned primary key auto_increment: drop the auto_increment primary key, set all id values null, then add the auto_increment primary key back in, whereupon MySQL will autofill the column sequentially ...

alter table tbl modify id int unsigned, drop primary key;
update tbl set id=null;
alter table tbl add primary key(id), modify id int unsigned auto_increment;

Note that you have to remove the auto_increment attribute before dropping the primary key, and restore the primary key before restoring the auto_increment attribute.

This won't do, though, if auto_increment values need to be ordered by another column value. To order an auto_increment ID on column othercol ...

alter table tbl modify id int unsigned, drop primary key;
set @count=0;
update tbl set id=(@count:=@count+1) order by othercol;
alter table tbl add primary key(id), modify id int unsigned auto_increment;


Return to the Artful Common Queries page