Reset some internal row's auto increment

Databases
1

I have 10 rows and deleted the third row. Now I have a gap in the id fields from 2 to 4. I have found out how to reset auto increment from the last (Max) row, but how do I reset it starting from an internal row. For example, after I deleted row 3, I would like like the rows to auto increment 1,2,3,4,5,6,7, 8,9 and not 1,2,4,5,6,7,8,9,10

Two things:

– I know it can be done manually in phpMyAdmin, but i need to do it through a script.
– alter table mytable AUTO_INCREMENT = 1; only works from the last row.

Thanks!

2

Why do you need to do that? Is it just that it bothers you that there’s a missing record?

changing the auto_increment will only reset it so that new records will get the reset value. But it won’t change existing records, nor will it fill in the gaps. The only way to really do that is to rebuild the records.

If you want that granular control on an identity field, then get rid of the auto increment field and use a straight int field.

3

Hmm. That really seems like a flaw in MySQL. Short-sighted. This should be an extra option ‘just because.’ Just because I need it to work like that. I forget sometimes that a team of programmers programmed Mysql and other languages and that they are products, much like a car, and only do what the original manufacturers intended and do not provide an entirely comprehensive set of solutions. Ugh.

How do I reset the value to existing records to an unbroken set of auto-incrementally ids? Someone somewhere must have done this.

People can hack into Bitcoin and steal millions of dollars – go so far as to install a backdoor into cold-storage wallets and potentially steal untouchable, offline funds. And I can’t close a gap in a Mysql database?

4

You can update the values of the existing records manually using a standard update statement (UPDATE tableName set ID = 3 WHERE ID=4). But if you want to do that every time you delete a record, you’re setting yourself up for a LOT of work. Then you also have to consider whether you’ve got foreign keys somewhere and ensure you don’t accidentally cause records to get matched up with the wrong records by changing one table and not the other.

It’s not that you CAN’T do it, it’s just that there isn’t any real tangible benefit to doing so…

5

Foreign keys are not a problem. Nothing will be influenced by the change. Much will be influenced by the gap. Fixing this manually is not really an option. How can I do it automatically?

<?php mysqlQuery {close that gap in the row} ?>
6

Perhaps find the key got got deleted, and loop through a foreach for every result AFTER the one deleted, and manually set that to the (uniqueID-1) through the alter table query?

Does that make sense? Makes sense in my head.

7

Yeah, sort of (not familiar with for each, but the looping got me to thinking) since I’m not working with, say, 10,000 rows, I could in table ‘original’ capture all the rows following the deletion, one by one, save them into table ‘temporary’ return to original table, delete all the rows following the deletion, (reset auto-increment because then I would be working from the max # of lines) return to ‘temporary’ table, copy all rows from temporary to original. All automatic and it would take a fraction of a second. All done.

Thanks RyanReese!!!

8

Yes you understand where I was going with it; albeit I wasn’t thinking in terms of 10000 rows (I have an 8 row table ATM and it’d probably be useful for me.)

Hopefully it works.

9

Actually, if it is just one table, a single UPDATE query should be enough. You have to make sure that it really don’t have relantionships with other tables though. As said before, it can cause all kinds of problems if other tables had any relationship with this one.

The fact that AUTO_INCREMENT doesn’t automatically uses (or shifts the ids of the rows with a higher id value than the one deleted) is not a mistake, or an error. All databases work like that… and it is with good reason.

I don’t understand your reasons but, as I said, just use an UPDATE instruction to update the value of the ID field when the original value is higher than the value of the id of the deleted record

10

So? Is this “gap” causing problems for your code or does it only bother you?

Keep in mind that to the database that field is an “identifier” and as long as they are all unique it doesn’t matter what they are or what order they are.

Analogy,
My house is the first on the street and it’s number is 4. My next door neighbor’s house is number 10.

Neither of us have any problem with mail deliveries.

1 Like
11

Excellent! That should work well!! Thanks!!

12

I just did this for my website (been meaning to do it for a while.)

Here is my script. Not the safest (yes yes I know I need to sanitize; getting around to it) but here you go. You can see the logic I used.

      $totalRowCount=mysqli_query($connection, "SELECT * FROM Subscribe");
      $totalRowResult=mysqli_num_rows($totalRowCount);
      $IDofModified=mysqli_query($connection,"SELECT PersonID FROM Subscribe WHERE Email='".$subscriberEmail."';");
   
      $IDofModifiedCount=mysqli_fetch_row($IDofModified);
      $delete=mysqli_query($connection, "DELETE FROM Subscribe WHERE Email='$subscriberEmail'");
      $countQuery=mysqli_query($connection, "SELECT COUNT(PersonID) FROM Subscribe");
      $count=mysqli_num_rows($countQuery);
      $resetIncrement=mysqli_query($connection,"ALTER TABLE Subscribe AUTO_INCREMENT=".$count[0]);
      for($i=($IDofModifiedCount[0]+1);$i<=$totalRowResult;$i++)
      {
        $englishVersion="UPDATE Subscribe SET PersonID='".($i-1)."' WHERE PersonID='".$i."'";
        $alterID=mysqli_query($connection,$englishVersion);
      }

Think that’s all you need to see. The for loop does all of it.

I did this because yes, it does annoy the crap out of me to see it not numbered right. Dunno OPs excuse though. I didn’t mind doing this work to get it right.

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.