MySQL AUTO_INCREMENT Counter giving some problem

Question

i have some MySQL tables which uses primary key with auto_increment feature, it all seems while inserting the records for the first time, but when i delete any records in between it does not reset the auto_increment counter but continues to use the last id.

for example i have inserted the records with following id's

1,2,3,4,5,6

when i delete 5,6 in between and next time while i insert another row it continues from 7. whereas i want it to continue from 5. it just does not reset the auto_increment counter, i tried modifying the table by querying this command ALTER TABLE amenities AUTO_INCREMENT = 1; still it does not seems to work.

Here is the sample DDL of one of the tables i got from phpMyAdmin Export.

CREATE TABLE `amenities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

It's a bad idea trying to change the natural behaviour of the auto_increment field. You could use a variable within your query or a counter programmatically if you want to show a progressive number. — Nicola Cossu, Jun 05 '11 at 12:01
Duplicate: http://stackoverflow.com/questions/2214141/mysql-auto-increment-after-delete — , Jun 05 '11 at 12:01

score 2 · Accepted Answer · answered Jun 05 '11 at 12:01

2

That's not how AUTO_INCREMENT works in MySQL or any other database. AUTO_INCREMENT always goes forward. Never re accommodates itself to fill the gaps.

If you need the behavior you are describing, you need to write your own code to handle that. In your client program, in the database or wherever you feel it's better. Just keep in mind that you will need to handle transactions very well in order to avoid concurrency problems when your database is on heavy load.

answered Jun 05 '11 at 12:01

Pablo Santa Cruz

176,835
32
241
292

i have read it somewhere some saying you could reset the counter, is not `AUTO_INCREMENT=5` the last line of my sample code meant for that purpose. – Ibrahim Azhar Armar Jun 05 '11 at 12:03
Yes, you could definitely reset the counter. But that's not **natural** when using `AUTO_INCREMENT`s. – Pablo Santa Cruz Jun 05 '11 at 12:04
aah, i never knew it is bad to do this, thank you for enlightening me about it. :) – Ibrahim Azhar Armar Jun 05 '11 at 12:06

MySQL AUTO_INCREMENT Counter giving some problem

1 Answers1