Как сбросить порядковые номера, чтобы они стали последовательными?

Question

У меня есть таблица mysql, где каждая строка имеет свой порядковый номер в столбце "sequence". Однако, когда строка удаляется, она оставляет пробел. Так...

1
2
3
4

... становится...

1
2
4

Есть ли удобный способ "сбрасывать" последовательность, чтобы она снова стала последовательной в одном запросе SQL?

Кстати, я уверен, что для этого процесса есть технический термин. Кто-нибудь?

ОБНОВЛЕНО: столбец "sequence" не является первичным ключом. Он используется только для определения порядка отображения записей в приложении.

Answer 1

Если поле является вашим основным ключом...

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

Теперь, что сказал...

---

Иначе...

Вполне возможно, что у вас есть другое поле (то есть, как и PK) для некоторого порядка, определенного приложением. Пока этот порядок не присущ другому полю (например, если он определен пользователем), в этом нет ничего плохого.

Вы можете воссоздать таблицу, используя (временный) auto_increment поле, а затем удалите auto_increment после этого.

Я бы соблазнился UPDATE в порядке возрастания и применить возрастающую переменную.

SET @i = 0;
UPDATE `table`
   SET `myOrderCol` = @i:=@i+1
 ORDER BY `myOrderCol` ASC;

^{(Запрос не проверен.)}

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

Вы можете уменьшить нагрузку, так что после удаления записи myOrderCol равный, скажем, 5:

SET @i = 5;
UPDATE `table`
   SET `myOrderCol` = @i:=@i+1
 WHERE `myOrderCol` > 5
 ORDER BY `myOrderCol` ASC;

^{(Запрос не проверен.)}

Это "перемешает" все последующие значения на единицу.

Answer 2

Я бы сказал, не беспокойся. Переназначение последовательных значений является относительно дорогой операцией, и если значение столбца предназначено только для целей заказа, то для этого нет веских оснований. Единственное, что вас может беспокоить, это если, например, ваш столбец UNSIGNED INT и вы подозреваете, что за время жизни вашего приложения у вас может быть более 4 294 967 296 строк (включая удаленные строки), и вы выйдете за пределы диапазона, даже если это вас касается может сделать переназначение как одноразовое задание через 10 лет, когда это произойдет.

Answer 3

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

Просто для целей обучения простой способ - сохранить ваши данные во временной таблице, обрезать исходную (это сбросить auto_increment) и затем заполнить ее.

Глупый пример:

create table seq (
id int not null auto_increment primary key,
col char(1)
) engine = myisam;

insert into seq (col) values ('a'),('b'),('c'),('d');

delete from seq where id = 3;

create temporary table tmp select col from seq order by id;

truncate seq;

insert into seq (col) select * from tmp;

но это абсолютно бесполезно.;)

Answer 4

Предполагая, что это поле идентификатора, вы можете сделать это, вставив:

INSERT INTO yourTable (ID)
SELECT MIN(ID)
FROM yourTable
WHERE ID > 1

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

Answer 5

Если это ваш ПК, вы не должны его менять. ПК должны быть (в основном) неизменными столбцами. Если бы вы изменили их, вам нужно было бы изменить их не только в этой таблице, но и во всех внешних ключах, где они есть.

Если вам нужна последовательная последовательность, спросите себя, почему. В таблице нет внутреннего или гарантированного порядка (даже в PK, хотя это может произойти из-за того, как большинство СУБД хранят и извлекают данные). Вот почему у нас есть ORDER BY предложение в SQL. Если вы хотите иметь возможность генерировать последовательные числа, основываясь на чем-то другом (время, добавленное в базу данных и т. Д.), Подумайте о том, чтобы сгенерировать это либо в своем запросе, либо с помощью внешнего интерфейса.