MySQL 8用户自定义变量问题：序列无间隙无重复校验方案求助

核心思路 #

要校验序列是否为连续无重复的1,2,3...，需要同时检测两种异常：

• 重复值：同一数值出现多次
• 间隙：数值不连续（包括序列未从1开始的情况）

MySQL 8支持的窗口函数（如ROW_NUMBER()、LAG()）可以高效完成这类校验，替代旧版依赖用户变量的方案。

方案1：精准定位所有异常记录 #

检测重复值 #

直接分组统计，找出出现次数大于1的数值：

SELECT my_seq_number, COUNT(*) AS duplicate_count
FROM my_table
GROUP BY my_seq_number
HAVING COUNT(*) > 1;

检测间隙 #

利用LAG()函数获取前一个序列值，计算差值判断间隙；同时检查序列是否从1开始：

SELECT 
    prev_value, 
    current_value, 
    current_value - prev_value AS gap_size
FROM (
    SELECT 
        my_seq_number AS current_value,
        LAG(my_seq_number) OVER(ORDER BY my_seq_number) AS prev_value
    FROM my_table
) t
WHERE prev_value IS NOT NULL AND current_value - prev_value > 1
UNION ALL
-- 检查序列起始值是否为1
SELECT 
    NULL AS prev_value,
    MIN(my_seq_number) AS current_value,
    MIN(my_seq_number) - 1 AS gap_size
FROM my_table
HAVING MIN(my_seq_number) > 1;

方案2：一键判断整体状态（是否有异常） #

如果只需要知道序列是否合规，不需要具体异常位置，可以用以下查询直接返回校验结果：

SELECT
    CASE WHEN duplicate_exists = 1 THEN '存在重复值' ELSE '无重复值' END AS duplicate_check,
    CASE WHEN gap_exists = 1 THEN '存在间隙' ELSE '无间隙' END AS gap_check
FROM (
    SELECT
        MAX(CASE WHEN duplicate_count > 1 THEN 1 ELSE 0 END) AS duplicate_exists,
        MAX(CASE WHEN gap_size > 0 THEN 1 ELSE 0 END) AS gap_exists
    FROM (
        -- 统计重复值标记
        SELECT my_seq_number, COUNT(*) AS duplicate_count, 0 AS gap_size
        FROM my_table
        GROUP BY my_seq_number
        UNION ALL
        -- 统计中间间隙标记
        SELECT 
            NULL AS my_seq_number,
            0 AS duplicate_count,
            current_value - prev_value AS gap_size
        FROM (
            SELECT 
                my_seq_number AS current_value,
                LAG(my_seq_number) OVER(ORDER BY my_seq_number) AS prev_value
            FROM my_table
        ) t
        WHERE prev_value IS NOT NULL AND current_value - prev_value > 1
        UNION ALL
        -- 统计起始间隙标记
        SELECT
            NULL AS my_seq_number,
            0 AS duplicate_count,
            MIN(my_seq_number) - 1 AS gap_size
        FROM my_table
        HAVING MIN(my_seq_number) > 1
    ) combined
) final_check;

方案3：快速定位所有异常位置 #

利用ROW_NUMBER()生成预期的连续序号，对比实际序列值，所有不匹配的记录即为异常（重复或间隙）：

SELECT
    my_seq_number,
    expected_rownum,
    '序列异常（间隙或重复）' AS status
FROM (
    SELECT 
        my_seq_number,
        ROW_NUMBER() OVER(ORDER BY my_seq_number) AS expected_rownum
    FROM my_table
) t
WHERE my_seq_number != expected_rownum;

方案优势 #

• 性能高效：窗口函数是MySQL 8原生优化的特性，数千行数据的校验毫秒级完成，远快于Java拉取数据校验
• 结果准确：同时覆盖重复值和间隙的检测，不会出现max-min+1=行数方法的漏判问题
• 维护简单：官方标准语法，避免用户变量在MySQL 8中可能出现的行为不确定性

内容的提问来源于stack exchange，提问作者yaki_nuka