prevent gaps in MySQL id field

I have a MySQL table with an auto incremement id field. When I delete a row and then insert a new row, The id of the row I deleted is skipped and the new gets an id of one greater than the previous row. Is there any way I can prevent this? I would lik开发者_Python百科e the new row to just replace the old one. Is there an important reason why this happens that I am missing?

---

The MySQL auto-increment function NEVER goes backward unless you force it to. And for a good reason. What if there was stray references to the missing records (logs, tables, etc...)?

You can force it by using this command:

ALTER TABLE tbl AUTO_INCREMENT = 1000;

Or, if you need to do it as part of the query:

LOCK TABLES tbl WRITE;
SELECT @id := MAX(id) FROM tbl;
INSERT INTO tbl SET id=@id, ...;
UNLOCK TABLES;

If you are using InnoDB, you could do this in a transaction instead...

Better to leave it be, however.

---

The definition of an autoincrement field is that every new row inserted is guaranteed to get a unique value. If you want to keep the old value then you must UPDATE the row instead of replacing it. If your design requires that autoincrement column values be contiguous then you will have to manage that yourself.

---

I'm sorry but I don't know the exact reason.

AFAIK you can't avoid that behavior unless you TRUNCATE the table or explicitly specify the id.