如何重置序列号以使其连续？

Question

我有一个 mysql 表，其中每一行在“序列”列中都有自己的序列号。然而，当一行被删除时，它会留下一个间隙。那么...

1
2
3
4

...变成...

1
2
4

有没有一种巧妙的方法来“重置”排序，以便它在一个 SQL 查询中再次变得连续？

顺便说一句，我确信这个过程有一个技术术语。有人吗？

更新：“序列”列不是主键。它仅用于确定记录在应用程序中显示的顺序。

Answer 1

如果该字段是您的主键...

...那么，正如该问题其他地方所述，您不应该更改 ID。 ID 已经是唯一的，您既不需要也不想重复使用它们。

现在，也就是说...

---

否则...

对于某些应用程序定义的排序，您很可能有一个不同字段（即，以及PK）。只要这个顺序不是其他字段所固有的（例如，如果它是用户定义的），那么就没有问题。

您可以使用（临时）auto_increment 字段重新创建表，然后删除 auto_increment。

我很想按升序更新并应用递增变量。

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;

^{（查询未测试。）}

这会将以下所有值“洗牌”1。

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

如果这是你的 PK 那么你不应该改变它。 PK 应该（大部分）是不变的列。如果您要更改它们，那么您不仅需要在该表中更改它，而且还需要在存在的任何外键中更改它。

如果您确实需要顺序序列，那么问问自己为什么。在表中，没有固有的或保证的顺序（即使在 PK 中，尽管由于大多数 RDBMS 存储和检索数据的方式而可能会出现这种情况）。这就是为什么我们在 SQL 中使用 ORDER BY 子句。如果您希望能够基于其他内容（添加到数据库中的时间等）生成序列号，那么请考虑在查询中或使用前端生成该序列号。

Answer 5

假设这是一个 ID 字段，您可以在插入时执行此操作：

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

正如其他人提到的，我不建议这样做。在评估下一个 ID 时，它将持有表锁。