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

发布于 2024-11-29 06:38:46 字数 274 浏览 7 评论 0原文

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

1
2
3
4

...变成...

1
2
4

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

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

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

I've got a mysql table where each row has its own sequence number in a "sequence" column. However, when a row gets deleted, it leaves a gap. So...

1
2
3
4

...becomes...

1
2
4

Is there a neat way to "reset" the sequencing, so it becomes consecutive again in one SQL query?

Incidentally, I'm sure there is a technical term for this process. Anyone?

UPDATED: The "sequence" column is not a primary key. It is only used for determining the order that records are displayed within the app.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

夢归不見 2024-12-06 06:38:46

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

...那么,正如该问题其他地方所述,您不应该更改 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。

If the field is your primary key...

...then, as stated elsewhere on this question, you shouldn't be changing IDs. The IDs are already unique and you neither need nor want to re-use them.

Now, that said...


Otherwise...

It's quite possible that you have a different field (that is, as well as the PK) for some application-defined ordering. As long as this ordering isn't inherent in some other field (e.g. if it's user-defined), then there is nothing wrong with this.

You could recreate the table using a (temporary) auto_increment field and then remove the auto_increment afterwards.

I'd be tempted to UPDATE in ascending order and apply an incrementing variable.

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

(Query not tested.)

It does seem quite wasteful to do this every time you delete items, but unfortunately with this manual ordering approach there's not a whole lot you can do about that if you want to maintain the integrity of the column.

You could possibly reduce the load, such that after deleting the entry with myOrderCol equal to, say, 5:

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

(Query not tested.)

This will "shuffle" all the following values down by one.

德意的啸 2024-12-06 06:38:46

我想说别打扰。重新分配顺序值是一项相对昂贵的操作,如果列值仅用于排序目的,则没有充分的理由这样做。您可能唯一担心的是,例如您的列是 UNSIGNED INT 并且您怀疑在应用程序的生命周期中您可能拥有超过 4,294,967,296 行(包括已删除的行)并且超出范围,即使这是您担心的10 年后发生这种情况时,可以将重新分配作为一次性任务。

I'd say don't bother. Reassigning sequential values is a relatively expensive operation and if the column value is for ordering purpose only there is no good reason to do that. The only concern you might have is if for example your column is UNSIGNED INT and you suspect that in the lifetime of your application you might have more than 4,294,967,296 rows (including deleted rows) and go out of range, even if that is your concern you can do the reassigning as a one time task 10 years later when that happens.

虐人心 2024-12-06 06:38:46

这是我经常在这里和其他论坛上看到的一个问题。正如 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;

但这完全没用。 ;)

This is a question that often I read here and in other forums. As already written by zerkms this is a false problem. Moreover if your table is related with other ones you'll lose relations.

Just for learning purpose a simple way is to store your data in a temporary table, truncate the original one (this reset auto_increment) and than repopulate it.

Silly example:

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;

but it's totally useless. ;)

秋千易 2024-12-06 06:38:46

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

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

If this is your PK then you shouldn't change it. PKs should be (mostly) unchanging columns. If you were to change them then not only would you need to change it in that table but also in any foreign keys where is exists.

If you do need a sequential sequence then ask yourself why. In a table there is no inherent or guaranteed order (even in the PK, although it may turn out that way because of how most RDBMSs store and retrieve the data). That's why we have the ORDER BY clause in SQL. If you want to be able to generate sequential numbers based on something else (time added into the database, etc.) then consider generating that either in your query or with your front end.

停顿的约定 2024-12-06 06:38:46

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

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

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

Assuming that this is an ID field, you can do this when you insert:

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

As others have mentioned I don't recommend doing this. It will hold a table lock while the next ID is evaluated.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文