MySQL主键缺失快速查找

mysql 主键id 123456789…… 中间缺一个，不连续了，如何快速找出来

方法1：使用自连接（推荐，性能较好）

SELECT t1.id + 1 AS missing_start FROM yizuotu_net t1 LEFT JOIN yizuotu_net t2 ON t1.id + 1 = t2.id WHERE t2.id IS NULL AND t1.id < (SELECT MAX(id) FROM yizuotu_net) ORDER BY t1.id LIMIT 1;

如果要找出所有缺失的ID：

SELECT t1.id + 1 AS missing_start,
       MIN(t2.id) - 1 AS missing_end
FROM your_table t1
JOIN your_table t2 ON t2.id > t1.id + 1
GROUP BY t1.id
HAVING missing_start <= missing_end;

方法2：使用临时表（适合小数据量）

-- 创建临时数字序列表
CREATE TEMPORARY TABLE numbers (n INT PRIMARY KEY);

-- 插入从1到最大ID的数字
INSERT INTO numbers
SELECT seq
FROM (
    SELECT @row := @row + 1 AS seq
    FROM your_table, (SELECT @row := 0) r
    LIMIT (SELECT MAX(id) FROM your_table)
) AS sequence;

-- 找出缺失的ID
SELECT n AS missing_id
FROM numbers
LEFT JOIN your_table ON numbers.n = your_table.id
WHERE your_table.id IS NULL;

方法3：使用窗口函数（MySQL 8.0+）

WITH gaps AS (
    SELECT 
        id,
        LEAD(id) OVER (ORDER BY id) AS next_id
    FROM your_table
)
SELECT id + 1 AS missing_start,
       next_id - 1 AS missing_end
FROM gaps
WHERE next_id > id + 1;

方法4：快速检查第一个缺失值

-- 找出最小的缺失ID
SELECT MIN(t1.id + 1) AS first_missing_id
FROM your_table t1
LEFT JOIN your_table t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL
  AND t1.id >= 1;