MySQL получает недостающие идентификаторы из таблицы

Ответ 1

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM testtable AS a, testtable AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

Надеюсь, эта ссылка также поможет http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

Ответ 2

Более эффективный запрос:

SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

Ответ 3

Чтобы добавить немного к Ивану, эта версия показывает номера, отсутствующие в начале, если 1 не существует:

SELECT 1 as gap_starts_at,
       (SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;

Ответ 4

Вышеуказанные запросы дадут два столбца, поэтому вы можете попробовать это, чтобы получить недостающие числа в одном столбце

select start from 
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) c order by start;

Ответ 5

Было бы гораздо эффективнее получить начало пропуска в одном запросе и конец пропуска в одном запросе.

У меня было 18 миллионов записей, и мне потребовалось меньше секунды, чтобы получить два результата. Когда я попытался собрать их вместе, мой запрос истек через час.

Получите начало разрыва:

SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS 
    (SELECT t2.id 
    FROM sequence t2 
    WHERE t2.id = t1.id + 1);

Получите конец разрыва:

SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS 
    (SELECT t2.id 
    FROM sequence t2 
    WHERE t2.id = t1.id - 1);    

Ответ 6

Вместо того, чтобы возвращать несколько диапазонов идентификаторов, если вы вместо этого хотите получить каждый отдельный отсутствующий идентификатор, каждый в отдельной строке, вы можете сделать следующее:

SELECT id+1 FROM table WHERE id NOT IN (SELECT id-1 FROM table) ORDER BY 1

Запрос очень эффективен. Однако он также включает в себя одну дополнительную строку в конце, которая равна наибольшему номеру идентификатора, плюс 1. Эта последняя строка может быть проигнорирована в скрипте вашего сервера, проверив количество возвращаемых строк (mysqli_num_rows), а затем используя цикл for если количество строк больше 1 (запрос всегда будет возвращать хотя бы одну строку).

Редактировать: я недавно обнаружил, что мое оригинальное решение не возвращает все пропущенные идентификационные номера, в тех случаях, когда пропущенные номера являются смежными (то есть, рядом друг с другом). Тем не менее, этот запрос все еще полезен для определения того, пропущены ли вообще цифры, очень быстро, и это поможет сэкономить время при использовании в сочетании с запросом hagensoft (верхний ответ). Другими словами, этот запрос может быть запущен первым, чтобы проверить отсутствие идентификаторов. Если что-то найдено, то запрос hagensoft может быть запущен сразу после этого, чтобы помочь определить точные идентификаторы, которые отсутствуют (время не сохраняется, но не намного медленнее). Если ничего не найдено, то потенциально экономится значительное количество времени, так как запрос hagensoft не нужно будет запускать.