自动删除主键序列中的间隙 [英] Automatically remove gaps in primary key sequence

问题描述

我正在创建网页。此网页基于用户操作将数据存储到MySQL数据库中。数据库有许多行。该行的主键是列rowID，并且它仅按顺序对行编号（例如，1,2,3,4 ....）。用户可以选择删除行。问题是，当用户删除除最后一行以外的行。在rowID中有一个跳过。例如，如果有5行，1，2，3，4，5，用户删除第三行。现在rowID列中的数据是1,2,4,5。我想知道是否已经有一个内置的函数在MySQL或PHP有效地为此帐户。

I am creating a webpage. This webpage stores data into a MySQL database based on the users actions. The database has many rows. The primary key of the row is the column rowID and it just numbers the rows in order (e.g. 1, 2, 3, 4....). The user has the option to delete rows. The problem is that when the user deletes rows other than the last row. There is a skip in the rowID. For example, if there are 5 rows, 1, 2, 3, 4 ,5 and the user deletes the third row. Now the data in the rowID column is 1, 2, 4, 5. I wanted to know if there is already a built in function in MySQL or PHP to account efficiently for this.

我知道的唯一方法是去循环遍历行，并刷新rowID基本上。然而，我觉得如果有大量的行，这将是非常昂贵的时间。

The only way I know to get rid of this is to loop through the rows and refresh the rowID basically. However I feel this would be very costly in terms of time if there are a large number of rows.

我还想知道如果创建了新行，如何自动增加rowID。目前我有代码找到当前最大的rowNumber并插入一个当前最大的rowNumber + 1的行。

I also wanted to know how to autoincrement the rowID if there are new rows being created. Currently I have code to find the current greatest rowNumber and insert a row with the current greatest rowNumber+1.

推荐答案

t想做到这一点。主键是唯一标识符，一旦创建就不应该更改。您要查找的是嵌套集。添加一个字段来跟踪left_id和right_id，如果必须，手动更新。有Doctrine2中的库为您处理这些。

You don't want to do that. The primary key is the unique identifier and should never change once created. What you're looking for is nested sets. Add a field to keep track of the left_id and right_id and manually update that if you must. There are libraries in Doctrine2 that handle this for you.

如果你绝对必须进行此更改，可以使用alter table删除该字段，然后重新添加。

If you absolutely MUST make this change, you can use alter table to drop the field and then re-add it.

这篇关于自动删除主键序列中的间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！