mysql 获取序列缺失

SELECT MAX(id) FROM your_table;

SELECT MIN(desired_id) - 1 AS missing_id
FROM (
    SELECT 1 AS desired_id UNION ALL SELECT 2 UNION ALL ... UNION ALL SELECT desired_max_id
) AS all_ids
WHERE desired_id NOT IN (SELECT id FROM your_table);

-- 创建一个示例表
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

-- 插入一些示例数据
INSERT INTO test_table (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- 查询当前最大ID
SET @max_id = (SELECT MAX(id) FROM test_table);

-- 计算并显示缺失的ID值（假设期望的ID范围是1到10）
SELECT MIN(desired_id) - 1 AS missing_id
FROM (
    SELECT 1 AS desired_id UNION ALL SELECT 2 UNION ALL ... UNION ALL SELECT 10
) AS all_ids
WHERE desired_id NOT IN (SELECT id FROM test_table);