Mysql 8 problem with user-defined variables

Question

I'm migrating my PLs from MySQL 5.7 to MySQL 8.

When I was starting with MySQL 5.7, I found a recipe to find gaps and repetitions in a number sequence.

In my table, it must not have gaps. It's right 1,2,3,4,5,6... but not 1,2,3,5,6... (4 is missing).

In my table, it must not have repetitions. It's wrong 1,2,3,3,4,5... (3 is repeated).

The query I found was like this, and it works perfectly for the target:

SET @a :=0;
SET @b :=1;
SELECT COUNT(DISTINCT LAG)
INTO GAPS
FROM (
        SELECT r.value, r.value - r2.value AS LAG
        FROM
        (SELECT if(@a, @a:=@a+1, @a:=1) as rownum, my_seq_number as value FROM my_table order by my_seq_number asc) AS r
        LEFT JOIN
        (SELECT if(@b, @b:=@b+1, @b:=1) as rownum, my_seq_number as value FROM my_table order by my_seq_number asc) AS r2
        ON r.rownum = r2.rownum
) T
WHERE T.LAG IS NOT NULL AND T.LAG <> 1 ;

@a, @b are user-defined variables, and it doesn't work fine (or work at all) in MySQL 8.

I'm wondering about making a count and check in Java, but it's slower than the query (I have thousands of rows).

I'm wondering about counting the number of rows (6), the min(1) and the max(6), and (max-min+1 = number of rows). But in the sequence 1,2,3,3,4,6, that formula is right but the sequence is wrong.

Do you have any other "recipe" or idea to check this in MySQL 8?

Any suggestion is welcome. Thank you.

Answer 1

Schematically:

WITH cte AS (
    SELECT column - LAG(column) OVER (ORDER BY column, unique_id) AS delta
    FROM table
)
SELECT SUM(delta > 1) AS gaps_amount,
       SUM(delta = 0) AS duplicates_amount
FROM cte;