i've got a mysql database/table and the first column is a smallint, autoincrement primary key.
how can i get my 'NextÂ_Autoindex' back to 121. it's on 143 now? there are 120 items in the table so the next entry i want to be 120. i used this:
and it seemingly worked. in phpMyAdmin it said 121 next to the next autoindex. but when a new entry is made it carries on from 143 !!! arhhhh. how annoying. what's going on there? how can i get the autoindex back to where it should be? thanks.
johnyboy, the problem isn't getting the autoincrement column to reset
straight up i will admit i don't know how to do that, but i'm sure you could drop and recreate the table as a last resort
the problem is wanting avoid gaps in the numbers
there are many reasons to leave the numbers alone, including some theoretical ones, and they have been discussed here before -- if you search diligently, you might even find those old threads, if you are interested
could you explain why you need to reset the numbers?
>but i'm sure you could drop and recreate the table as a last resort
that's what i've ended up doing, but i've come accross this problem a few times and dropping and recreating seems a bit of a sledgehammer approach. there must be a way to reset/set the 'next autoindex' value?
the reason i've ended up in this situation is i accidentally added to my table (i'm adding via an automated piece of php code) and in rejigging some things i ended up adding twice. so i deleted the 2nd add and ended up with the auto increment completely out of sync
>could you explain why you need to reset the numbers?
my php code that reads from the table relies on the id column being numbered 1,2,3,4,5 etc. so when there's a gap it goes wrong basically. it worked fine and works fine accept when i need to delete lines for any reason, as i've just found out