Resetting Auto Increment in MYSQL

[OmniX]

Due to the compability between PHP and MYSQL I am hoping someone has a solution to my problem :(

I wish to reset the auto increment field and get them "redone" as such.

Problem being:
When I delete a row it leaves an incorrect auto increment, as a total as one has been removed and when I add other fields it is always one, two, etc less.

Example:
Row 10
Row 11
Row 12
Row 13

I delete row 13, and insert another starts at 14... (when it should start at 13)
I delete row 12, and insert another starts at 14... (when it is 13 minus 1)

Anyways, Thanks for the help.

Regards, X

PS: I am using phpMyAdmin

[darkagn]

In most MySQL engines the auto_increment function assigns unique IDs to new rows inserted into a table. If one is deleted, the ID has still been used, so the next one to be used is still 14 in your example because 13 and 12 have been used (although they have been deleted). As far as I know, it is not possible to "reset" the increment without deleting the table, recreating it and reinserting the data.

[OmniX]

Thats what I have been told.

But that is either a bug or stupid as I cannot see any purpose of not being able to reset or some sort of fix as it makes your database invalid... :|

[zubairbhatti135]

i think if u want an increment that should start with 14 after u have deleted 13 number. then u will have to reset ur increment with start with 13 and increment by 1. thanks

[OmniX]

its starts at 1 and goes through to x...

[darkagn]

Thats what I have been told.

But that is either a bug or stupid as I cannot see any purpose of not being able to reset or some sort of fix as it makes your database invalid... :|

I wouldn't say that it makes a database invalid, maybe just not neat and tidy. I think that it is done this way for efficiency reasons. Imagine in a large database having to keep track of every ID for every table that uses auto_increment facility. Rather than just remembering that TableX is up to 14, you would have to store the fact that the table's maximum ID is 13 but that 4, 7, and 12 have been deleted for example. That would greatly slow inserts down and also take up a lot more space.

I do see your point of view though, it certainly isn't exactly what you would think an auto index should do on the surface. :)

[darkagn]

Also, you don't have to use the auto_increment facility just because the table has it. You can insert a row and set the ID manually, assuming you know what you want to set it to...

[OmniX]

Ya na I agree but there should be a reset feature or something.

Like redos all the rows from 1 to x.

Thats what I am doing now (modifying the rows when required) but it is very annoying and time consuming over something stupid/bug :S

(Imagine how many times you accidently delete a row and that *()# up your whole table)

:@

[Moderns]

Any solution guys? I am still facing the same issue..

[graycubed]

Hi,
I came across the same problem.
here is my solution which worked for me.
my database is called products and my table is bikes.

$result = mysql_query("SELECT * FROM bikes");
$rownew = 0;
while ($row = mysql_fetch_array($result)) {
	$itempos = $row["item_position"];
	mysql_query("UPDATE `products`.`bikes` SET `item_position` = {$rownew} WHERE `bikes`.`item_position` ={$itempos} LIMIT 1");
	$rownew++;
}
//reset auto increment
mysql_query("ALTER TABLE bikes AUTO_INCREMENT = 1");

This resets the values of my item_position in order and then resets the auto increment.
For me the item position starts at 0 but if you change it to $rownew=1 it will start at 1.
Run this every time you delete a row and you should be fine.
Cheers hope this works for you!

[virus2801]

nice! thank you!

[iffy85]

Imagine if such a reset feature existed and the auto increment value was reset and its deleted id was reused by a different user, what's gonna happen if you try to rollback the delete or something. I guess that kind of feature does not exist to prevent such confusion