set the 'Next Autoindex' for my mysql table?

[johnyboy]

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:

SET INSERT_ID=121;

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.

[r937]

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?

rudy

[johnyboy]

thanks for the reply rudy

>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

[r937]

>> "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"

then perhaps you need to fix your php code to work with gaps, rather than fix your table to constantly eliminate gaps

what happens if you delete id 2? are you going to renumber all the ids from 3 up?


rudy

[johnyboy]

i'm never going to want to delete from this table (except if an accident occurs and adds extra like happend just recently)

ok thanks anyway