MySql  auto increment

perfectobject
perfectobject used Ask the Experts™
on
hello experts,

how to reset the auto increment column in MySql..!!

For Example If I have entered 10 records, and deleted center records – 4th, 5th. I want to insert next record as 4th not 11th.

Thanks..!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
you have to create a trigger (insert) and set the id as

select min(isnull(id1,0))+1 as newid from(
select t1.id id1, t2.id id2
from mytable t1, mytable t2
where t2.id=t1.id+1
) x where id2 is null order by id1 limit 1
Read carefully here-
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
There are many consideration in this case, so best thing is to calculate it in program and update at the time of insertion.
theGhost_k8Database Consultant

Commented:
ALTER TABLE tbl AUTO_INCREMENT = <VALUE>;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial