Как заполнить "дыры" в полях автоматического увеличения?

Ответ 1

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

Ответ 2

Я согласен с @Aaron Digulla и @Shane N. Разрывы бессмысленны. Если они DO означают что-то, это ошибочный дизайн базы данных. Период.

Если вам абсолютно необходимо заполнить эти отверстия, И, вы используете как минимум MySQL 3.23, вы можете использовать TEMPORARY TABLE для создания нового набора идентификаторов. Идея здесь состоит в том, что вы собираетесь выбрать все ваши текущие идентификаторы, чтобы упорядочить их во временную таблицу:

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.

Как только это будет сделано, вам необходимо обновить любую другую ссылку на Id в "OldTable" и любом внешнем ключе, который он использует. Для этого вам, вероятно, понадобится УБЫТЬ любые ограничения внешнего ключа, которые у вас есть, обновить любую ссылку в таблицах от OldId до NewId, а затем повторно ввести ограничения внешнего ключа.

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

UPDATE: добавление примера обновления идентификаторов

Например:

Скажем, у вас есть следующие две схемы таблиц:

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

И вот что.

Ответ 3

Вам вообще не нужно заботиться о пробелах. Если вы добираетесь до конца типа данных для идентификатора, это должно быть относительно простым для ALTER таблицы для перехода на следующий самый большой тип 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

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

Ответ 4

INSERT INTO prueba(id) 
VALUES (
(SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target))

IFNULL для пропусков null в нулевом числе строк

добавить цель для skip error mysql "предложение FROM)

Ответ 5

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

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

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

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

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

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

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

Ответ 6

Как говорили другие, это не имеет значения, и если это произойдет, то что-то не так в вашем дизайне базы данных. Но лично мне просто нравится, что они все в порядке!

Вот несколько SQL, которые будут воссоздавать ваши идентификаторы в том же порядке, но без пробелов.

Это делается сначала в поле temp_id (которое вам нужно создать), поэтому вы можете видеть, что все это хорошо, прежде чем перезаписывать старые идентификаторы. Замените Tbl и id, если это необходимо.

SELECT @i:=0;
UPDATE Tbl
JOIN
(
    SELECT id
    FROM Tbl
    ORDER BY id
) t2
ON Tbl.id = t2.id
SET temp_id = @i:[email protected]+1;

Теперь у вас будет поле temp_id со всеми вашими блестящими новыми идентификаторами. Вы можете заставить их жить просто:

UPDATE Tbl SET id = temp_id;

И затем опустив столбец temp_id.

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

Ответ 7

Возможно, вы захотите очистить пробелы в столбце приоритета. Ниже приведено поле автоматического увеличения для приоритета. Дополнительное левое соединение в одном и том же столбце позволит убедиться, что оно добавлено в том же порядке, что и (в данном случае) приоритет

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:[email protected]+1)
    ORDER BY tbl.priority
)