CODEWITHSUNDEEP
mysql
Kevmeister
Kevmeister

Reputation: 147

MySQL auto-increment column - cannot re-use numbers after deleting records

I have a table with an auto-increment integer field "ID". Let's say there's a hundred records in it, and I delete records 91 - 100. The next record I create I'd expect the ID field to be 91, but it isn't, it's 101. Is there any way to get around this? Thanks guys.

Upvotes: 2

Views: 595

Answers (2)

Omesh
Omesh

Reputation: 29121

You can manually set auto_increment after delete operation as:

ALTER TABLE table_name AUTO_INCREMENT = 91;

Upvotes: 2

juergen d
juergen d

Reputation: 204924

You can remove the auto-imcrement from your table and set the id manually every time you are inserting something

@maxid = select max(id) from your_table
insert into your_table (id) values (@maxid+1)

Upvotes: 1

Related Questions