when you delete a record from a database mysql

i'm new to the whole database scenario and just learning. I have a database mysql and i am using phpmyadmin to edit fields etc. I created a auto_incremenet 'id' field that is set as a primary. If i add 5 开发者_运维技巧fields, the id will be '1,2,3,4,5'. If i delete all those fields and add another 5 fields, why does the ID go to '6,7,8,9,10'? (instead of going back to 0, since technically the other fields do not exist anymore so its unique right?). Is there a way to make the id be sequentially numeric? 1,2,3,4,5 ?

---

You don't need to worry about it. Your primary key should be an unsigned int which can hold large enough numbers.

---

I believe this happens because when using a relational database system, you can "link" rows to other rows, usually by their id. If you start reusing IDs, then you might end up in confusing situations.

You can reset the auto_increment counter to whatever you wish using the following query:

ALTER TABLE tableName AUTO_INCREMENT=123

If you are deleting all rows in your table, you can use TRUNCATE which will also reset the counter.

TRUNCATE TABLE tableName

---

I think the main reason for this behaviour is efficiency, it means that MySQL doesn't need to track which numbers are not used, it only need to know which number was last used.

---

Have a look at this question. But basically:

ALTER TABLE mytable AUTO_INCREMENT = 1;

---

UPDATE

As mentioned, fiddling with auto generated PKs will indefinitely cause you a lot of headache. Or writing a bunch of boilerplate code to do housekeeping.

---

You will have to reset the auto increment, so you can renumber the auto_incremented values, read more about the solutions here http://www.dbuggr.com/milly/reset-auto-increment-mysql/.