[Solved] mysql : check if column id is Consecutive [closed]

by


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;

2

solved mysql : check if column id is Consecutive [closed]