如何重置序列号成为连续的？ [英] How do I reset sequence numbers to become consecutive?

问题描述

我有一个mysql表，其中每行在sequence列中都有自己的序列号。但是，当一行被删除时，它会留下一个空白。所以...

  1 
 2 
 3 
 4 
  

...成为...

  1 
 2 
 4 
  

有没有一个整洁的方法重置排序，所以在一个SQL查询中再次连续使用？

顺便说一下，我确定这个过程有一个技术术语。任何人？

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

解决方案

如果该字段是您的主键

...那么，就这个问题的其他地方所说，你不应该改变ID。 ID已经是唯一的，你不需要也不需要重新使用它们。

现在，说...

---

否则...

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

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

我会以升序诱惑 UPDATE ，并应用递增变量。

  SET @i = 0; 
更新`table` 
 SET`myOrderCol` = @i：= @ i + 1 
 ORDER BY`myOrderCol` ASC; 
  

^{（查询未测试。）}

每次删除项目时，这样做似乎相当浪费，但不幸的是，使用这种手动订购方法，如果您想维护列的完整性。

您可以减少加载，以便删除 myOrderCol 之后的条目例如， 5 ：

  SET @i = 5; 
更新`table` 
 SET`myOrderCol` = @i：= @ i + 1 
 WHERE`myOrderCol`> 5 
 ORDER BY`myOrderCol` ASC; 
  

^{（查询未测试。）}

这将随机播放所有下列值一个。

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.

解决方案

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.

这篇关于如何重置序列号成为连续的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！