Mysql 8用户自定义变量问题

huangapple go评论63阅读模式
英文:

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 &lt;&gt; 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 &gt; 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 &gt; 1) AS gaps_amount,
       SUM(delta = 0) AS duplicates_amount
FROM cte;

huangapple
  • 本文由 发表于 2023年7月13日 15:34:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76676957.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定