Как заполнить “дыры” в auto-incremenet полях?

Обычно вам не нужно заботиться о пробелах. Если вы подходите к концу типа данных для идентификатора, должно быть относительно легко ИЗМЕНИТЬ таблицу, чтобы перейти к следующему по величине типу int.

Если вам абсолютно необходимо начать заполнять пробелы, вот запрос, чтобы вернуть наименьшее доступный идентификатор (надеюсь, не слишком медленно):

SELECT MIN(table0.id)+1 AS newid
FROM table AS table0
LEFT JOIN table AS table1 ON table1.id=table0.id+1
WHERE table1.id IS NULL

(не забудьте использовать транзакцию и / или перехватить повторяющиеся вставки ключей, если вам нужны одновременные вставки для работы.)

Я согласен с @Aaron Digulla и @Shane N. Пробелы бессмысленны. Если они ДЕЙСТВИТЕЛЬНО что-то значат, это ошибочный дизайн базы данных. Период.

При этом, если вам абсолютно НЕОБХОДИМО заполнить эти дыры, И вы используете как минимум MySQL 3.23, вы можете использовать ВРЕМЕННУЮ ТАБЛИЦУ для создания нового набора идентификаторов. Идея здесь в том, что вы собираетесь выбрать все свои текущие идентификаторы по порядку во временной таблице как таковой:

CREATE TEMPORARY TABLE NewIDs
(
    NewID INT UNSIGNED AUTO INCREMENT,
    OldID INT UNSIGNED
)

INSERT INTO NewIDs (OldId)
SELECT
    Id
FROM
    OldTable
ORDER BY
    Id ASC

Это даст вам таблицу, сопоставляющую ваш старый идентификатор с совершенно новым идентификатором, который будет последовательным по сути, благодаря свойству AUTO INCREMENT столбца NewId.

Как только это будет сделано, вам необходимо обновить любую другую ссылку на идентификатор в «OldTable» и любой внешний ключ, который он использует. Сделать это, вам, вероятно, потребуется ОТБРАСЫВАТЬ любые имеющиеся у вас ограничения внешнего ключа, обновить любую ссылку в таблицах с OldId на NewId, а затем повторно установить ограничения внешнего ключа.

Однако я бы сказал, что вам не следует этого делать ЛЮБОЙ из этого, и просто поймите, что ваше поле Id существует с единственной целью ссылки на запись и не должно НЕ иметь какое-либо конкретное отношение.

ОБНОВЛЕНИЕ: Добавление примера обновления Ids

Например:

Допустим, у вас есть две следующие схемы таблиц:

CREATE TABLE Parent
(
    ParentId INT UNSIGNED AUTO INCREMENT,
    Value INT UNSIGNED,
    PRIMARY KEY (ParentId)
)

CREATE TABLE Child
(
    ChildId INT UNSIGNED AUTO INCREMENT,
    ParentId INT UNSIGNED,
    PRIMARY KEY(ChildId),
    FOREIGN KEY(ParentId) REFERENCES Parent(ParentId)
)

Теперь в родительской таблице появляются пробелы.

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

CREATE TEMPORARY TABLE NewIDs
(
    Id INT UNSIGNED AUTO INCREMENT,
    ParentID INT UNSIGNED
)

INSERT INTO NewIDs (ParentId)
SELECT
    ParentId
FROM
    Parent
ORDER BY
    ParentId ASC

Затем нам нужно указать MySQL игнорировать ограничение внешнего ключа, чтобы мы могли правильно ОБНОВИТЬ наши значения. Мы будем использовать этот синтаксис:

SET foreign_key_checks = 0;

Это заставляет MySQL игнорировать проверки внешнего ключа при обновлении значений, но он по-прежнему будет обеспечивать использование правильного типа значения (подробности см. В Справочник MySQL ).

Затем нам нужно обновить наши родительские и дочерние таблицы новыми значениями. Для этого мы будем использовать следующий оператор UPDATE:

UPDATE
    Parent,
    Child,
    NewIds
SET
    Parent.ParentId = NewIds.Id,
    Child.ParentId = NewIds.Id
WHERE
    Parent.ParentId = NewIds.ParentId AND
    Child.ParentId = NewIds.ParentId

Теперь мы обновили все наши значения ParentId правильно на новые упорядоченные идентификаторы из нашей временной таблицы. Как только это будет завершено, мы можем повторно установить наши проверки внешнего ключа для поддержания ссылочной целостности:

SET foreign_key_checks = 1;

Наконец, мы отбросим нашу временную таблицу, чтобы очистить ресурсы:

DROP TABLE NewIds

И это все.

нам нужно обновить наши родительские и дочерние таблицы новыми значениями. Для этого мы будем использовать следующий оператор UPDATE:

UPDATE
    Parent,
    Child,
    NewIds
SET
    Parent.ParentId = NewIds.Id,
    Child.ParentId = NewIds.Id
WHERE
    Parent.ParentId = NewIds.ParentId AND
    Child.ParentId = NewIds.ParentId

Теперь мы правильно обновили все наши значения ParentId на новые упорядоченные идентификаторы из нашей временной таблицы. Как только это будет завершено, мы можем повторно установить наши проверки внешнего ключа для поддержания ссылочной целостности:

SET foreign_key_checks = 1;

Наконец, мы отбросим нашу временную таблицу, чтобы очистить ресурсы:

DROP TABLE NewIds

И все.

нам нужно обновить наши родительские и дочерние таблицы новыми значениями. Для этого мы будем использовать следующий оператор UPDATE:

UPDATE
    Parent,
    Child,
    NewIds
SET
    Parent.ParentId = NewIds.Id,
    Child.ParentId = NewIds.Id
WHERE
    Parent.ParentId = NewIds.ParentId AND
    Child.ParentId = NewIds.ParentId

Теперь мы правильно обновили все наши значения ParentId на новые упорядоченные идентификаторы из нашей временной таблицы. Как только это будет завершено, мы можем повторно установить наши проверки внешнего ключа для поддержания ссылочной целостности:

SET foreign_key_checks = 1;

Наконец, мы отбросим нашу временную таблицу, чтобы очистить ресурсы:

DROP TABLE NewIds

И это все.

Есть простой способ, но он не работает: просто попробуйте вставить с идентификатором, а если это не удастся, попробуйте следующий.

Или выберите идентификатор и когда вы не получите результата, используйте его.

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

[EDIT] Если это не первичный ключ, вы можете использовать этот оператор обновления:

update (
    select *
    from table
    order by reg_id -- this makes sure that the order stays the same
)
set reg_id = x.nextval

, где x - новая последовательность, которую вы должны создать. Это изменит нумерацию всех существующих элементов с сохранением порядка. Это не удастся, если у вас есть ограничения внешнего ключа. И это приведет к повреждению вашей базы данных, если вы укажете эти идентификаторы в любом месте без ограничений внешнего ключа.

Обратите внимание, что во время следующей вставки база данных создаст огромный пробел, если вы не сбросите столбец идентификаторов.

Возможно, вы захотите убрать пробелы в колонке приоритетов. Приведенный ниже способ позволит создать поле с автоматическим увеличением приоритета. Дополнительный левый join на той же вкладке обеспечит добавление в том же порядке, что и приоритет

SET @a:=0;
REPLACE INTO footable
 (id,priority)
    (
    SELECT tbl2.id, @a 
    FROM footable as tbl
    LEFT JOIN footable as tbl2 ON tbl2.id = tbl.id  
    WHERE (select @a:=@a+1)
    ORDER BY tbl.priority
)