mysql : check if column id is Consecutive [closed]

I have a table that contains IDs.

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  9 |
|  7 |
| 10 |

Number 5 and number 8 are not in the table. I want to select only those rows from the table

Answer

You can get the gaps by looking at the previous row. If your table is not too large:

select (t.prev_id + 1) as first_missing,
       (t.id - 1) as last_missing,
       (t.id - t.prev_id - 1) as cnt
from (select t.*,
             (select max(t2.id) 
              from t t2
              where t2.id < t.id
             ) as prev_id
      from t
     ) t
where t.prev_id <> t.id - 1;

Actually splitting this out into separate rows is tricky in earlier versions of MySQL unless you have a number or tally table.

If your data is large, you can use variables instead:

select (t.prev_id + 1) as first_missing,
       (t.id - 1) as last_missing,
       (t.id - t.prev_id - 1) as cnt
from (select t.*,
             (case when (@temp := @prev) = null
                   then null  -- never happens
                   when (@prev := id) = null       
                   then null  -- never happens
                   else @temp
               end) as prev_id
      from (select t.* from t order by id) t cross join
           (select @prev := -1) params
     ) t
where t.prev_id <> t.id - 1;