Automatically remove spaces in primary key sequence

Question

I am creating a web page. This web page stores data in a MySQL database based on user actions. The database has many rows. The row’s primary key is the rowID of the row, and it simply arranges the rows in order (for example, 1, 2, 3, 4 ....). The user has the ability to delete lines. The problem is that when the user deletes lines other than the last line. Space in lineID. For example, if there are 5 lines, 1, 2, 3, 4, 5, and the user deletes the third line. 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 effectively account for this.

The only way I know to get rid of this is to iterate over the rows and update the rowID basically. However, I believe that it will be very expensive in terms of time if there are a large number of rows.

I also wanted to know how rowID auto-increment if new rows are created. I currently have code to search for the current largest rowNumber and insert a row with the current largest rowNumber + 1.

Answer 1

You do not want to do this. The primary key is a unique identifier and should never change after it is created. What you are looking for is nested sets . Add a field to track left_id and right_id and manually update it if necessary. There are libraries in Doctrine2 that handle this for you.

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

Answer 2

No, no, because it doesn’t matter. You should not depend on the fact that the primary key is a sequential sequence. Its only function is to ensure that the key is a unique identifier for the string, except that the numbers do not matter.