英文:
Mysql 8 problem with user-defined variables
问题
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.
英文:
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.
答案1
得分: 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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论