Как найти пробелы в идентификаторах в наборе записей MySQL?

Проблема здесь связана с другим вопросом, который у меня был...

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

Я хочу найти пробелы и повторно использовать заброшенные идентификаторы.

Какой эффективный способ сделать это в MySQL?


mysql gaps-and-islands recordset
person qodeninja    schedule 09.12.2011    source источник
comment
Связано: stackoverflow.com/questions/3718229/   -  person Frosty Z    schedule 09.12.2011
comment
Если вы используете INT для своего первичного ключа, у вас может быть более 2 миллиардов записей. Зачем пытаться заполнить пробелы? У вас закончились номера? Я считаю, что есть преимущество в том, чтобы знать, что числа соответствуют порядку добавления записей.   -  person minboost    schedule 09.12.2011
comment
Возможно, вы столкнетесь с меньшими проблемами производительности, изменив тип первичного ключа на BIGINT (если 4 миллиарда значений, предоставляемых INT, слишком мало), чем пытаться повторно использовать идентификаторы в очень большой таблице.   -  person Frosty Z    schedule 09.12.2011
comment
+1 за хороший отзыв здесь. Я не подумал, что, может быть, лучше просто не беспокоиться о пробелах.   -  person qodeninja    schedule 09.12.2011
comment
У некоторых других до вас была идея повторного использования заброшенных идентификаторов (в некоторых случаях идентификационные номера граждан, принадлежащие умершим людям), и это разумное решение привело к бесконечному количеству проблем для тех людей, которые унаследовали повторно использованные идентификаторы. Я бы ни в коем случае не рекомендовал заниматься подобным.   -  person jap1968    schedule 09.12.2011
comment
@ jap1968 jap1968, снова прочитав этот комментарий, заставил меня посмеяться, он действительно хорошо иллюстрирует проблему.   -  person qodeninja    schedule 22.01.2014

Ответы (4)


arrow_upward
17
arrow_downward

Прежде всего, какое преимущество вы пытаетесь получить, повторно используя пропущенные значения? Обычный INT UNSIGNED позволит вам сосчитать до 4 294 967 295. С «миллионами записей» ваша база данных должна вырасти в тысячу раз, прежде чем закончатся действительные идентификаторы. (А затем использование BIGINT UNSIGNED увеличит вас до 18 446 744 073 709 551 615 значений.)

Попытка переработать значения, пропущенные MySQL, вероятно, отнимет у вас много времени, пытаясь компенсировать то, что на самом деле не беспокоит MySQL.

С учетом сказанного вы можете найти отсутствующие идентификаторы с помощью чего-то вроде:

SELECT id + 1
FROM the_table
WHERE NOT EXISTS (SELECT 1 FROM the_table t2 WHERE t2.id = the_table.id + 1);

Это найдет только первое пропущенное число в каждой последовательности (например, если у вас есть {1, 2, 3, 8, 10}, будет найдено {4,9}), но это, вероятно, будет эффективным, и, конечно, после того, как вы ввели идентификатор, вы можете всегда запускайте его снова.

person VoteyDisciple    schedule 09.12.2011
comment
если 1 - это первый пробел, он не будет возвращен - person morandi3; 15.03.2013
comment
В моем случае важно каждое пропущенное число, как и последний абзац ответа :) +1 Upvote - person AamirR; 01.03.2017

arrow_upward
2
arrow_downward

Следующее вернет строку для каждого промежутка в целочисленном поле «n» в mytab:

/* cs will contain 1 row for each contiguous sequence of integers in mytab.n
   and will have the start of that chain.
   ce will contain the end of that chain */
create temporary table cs (row int auto_increment primary key, n int);
create temporary table ce like cs;
insert into cs (n) select n from mytab where n-1 not in (select n from mytab) order by n;
insert into ce (n) select n from mytab where n+1 not in (select n from mytab) order by n;
select ce.n + 1 as bgap, cs.n - 1 as egap
  from cs, ce where cs.row = ce.row + 1;

Если вместо пробелов вам нужны непрерывные цепочки, то окончательный выбор должен быть:

select cs.n as bchain, ce.n as echain from cs,ce where cs.row=ce.row;
person ccc    schedule 30.12.2012
comment
второй запрос ''выберите cs.n как bchain, ce.n как echain из cs,ce, где cs.row=ce.row;'' соединение отображает больший разрыв, который на самом деле существует, но первый работает отлично. - person magdmartin; 02.12.2013

arrow_upward
1
arrow_downward

Это решение лучше, если вам нужно включить первый элемент как 1:

SELECT
    1 AS gap_start,
    MIN(e.id) - 1 AS gap_end
FROM
    factura_entrada e
WHERE
    NOT EXISTS(
        SELECT
            1
        FROM
            factura_entrada
        WHERE
            id = 1
    )
LIMIT 1
UNION
    SELECT
        a.id + 1 AS gap_start,
        MIN(b.id)- 1 AS gap_end
    FROM
        factura_entrada AS a,
        factura_entrada AS b
    WHERE
        a.id < b.id
    GROUP BY
        a.id
    HAVING
        gap_start < MIN(b.id);
person jalopezsuarez    schedule 17.06.2013

arrow_upward
0
arrow_downward

Если вы используете MariaDB, у вас есть более быстрый вариант

SELECT * FROM seq_1_to_50000 where seq not in (select col from table);

документы: https://mariadb.com/kb/en/mariadb/sequence/

person Moshe L    schedule 10.01.2017