Auto Increment after delete in MySQL

Asked 2010-02-06T18:21:01.360

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'.

I have:

table: course
fields: courseID, courseName

Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.

My SQL knowledge isn't amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?

This table will relate to others in a database.

Given all the advice, I have decided to ignore this 'problem'. I will simply delete and add records whilst letting the auto increment do it's job. I guess it doesn't really matter what the number is since it's only being used as a unique identifier and doesn't have a (as mentioned above) business meaning.

For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.

Edit Report

2 Answers

ALTER TABLE foo AUTO_INCREMENT=1

If you've deleted the most recent entries, that should set it to use the next lowest available one. As in, as long as there's no 19 already, deleting 16-18 will reset the autoincrement to use 16.

EDIT: I missed the bit about phpmyadmin. You can set it there, too. Go to the table screen, and click the operations tab. There's an AUTOINCREMENT field there that you can set to whatever you need manually.

answered 2010-02-06T18:25:18.423

you can select the ids like so:

set @rank = 0;
select id, @rank:=@rank+1 from tbl order by id

the result is a list of ids, and their positions in the sequence.

you can also reset the ids like so:

set @rank = 0;
update tbl a join (select id, @rank:=@rank+1 as rank from tbl order by id) b
  on a.id = b.id set a.id = b.rank;

you could also just print out the first unused id like so:

select min(id) as next_id from ((select a.id from (select 1 as id) a
  left join tbl b on a.id = b.id where b.id is null) union
  (select min(a.id) + 1 as id from tbl a left join tbl b on a.id+1 = b.id
  where b.id is null)) c;

after each insert, you can reset the auto_increment:

alter table tbl auto_increment = 16

or explicitly set the id value when doing the insert:

insert into tbl values (16, 'something');

typically this isn't necessary, you have count(*) and the ability to create a ranking number in your result sets. a typical ranking might be:

set @rank = 0;
select a.name, a.amount, b.rank from cust a,
  (select amount, @rank:=@rank+1 as rank from cust order by amount desc) b
  where a.amount = b.amount

customers ranked by amount spent.

answered 2010-02-06T18:23:00.887

Auto Increment after delete in MySQL

2 Answers

Your Answer