MySql gets row if id is in sequence

Question

We need to get the string if it product_idis in sequence.

Table (product)

product_id    name

    1         Parle
    2         Coconut
    3         Pizza
    4         Colgate
    5         Ice Cream
    6         Nuts
    8         Britania
    9         Pepsi

Demand exit

product_id    name

    1         Parle
    2         Coconut
    3         Pizza
    4         Colgate
    5         Ice Cream
    6         Nuts

product_id - 8 and 9 fails because it is not in sequence.

My attempt

select distinct t1.*, t1.product_id, (select GROUP_CONCAT(t2.product_id) from product as t2) as p_id
from product t1
having FIND_IN_SET(t1.product_id+1, p_id)

Output

product_id    name

    1         Parle
    2         Coconut
    3         Pizza
    4         Colgate
    5         Ice Cream

In this attempt, I do not get the string product_id - 6.

Note. I want a MySqlrequest not in PHP.

Thank!

Answer 1

One way I can imagine is a user defined user-defined variable to get the row rank and then calculate the difference between the identifier and the product rank and select only those rows where the difference = 0

select *
from(
  select f.*,@row:= @row + 1 rank,
  product_id - @row as diff
  from product f,
  (select @row:= 0) t
  order by product_id
) t1
where diff = 0

Demo

, no.

select *
 from(
  select f.*,@row:= @row + 1 rank,
  product_id - @row as diff
  from product f,
  (select @row:= (select min(product_id)  from product_sale_flag) a) t
  order by product_id
) t1
where diff = -1

product_id @row, rank , product_id , , product_id lastly, , , 0, , . , .