mysql自动增量,MySQL中的自动增量

I have one of my primary key column in my table to auto-increment. However when I delete a row from the table that has the highest primary key id (lets say 11). Then the next time I do an insertion it inserts the key as 12 not 11 (though logically it can use 11 as there is no entry associated with the key 11). How can I make this happen?

解决方案

Are you really sure you want this? An autoincrement column will guarantee a unique number, and that's enough. You could update the next autoincrement value I guess (i'll have to look it up how that works), but I don't think you should want that.

If you need to control the numbers in a column, you should do so manually.

nevertheless, you can change the autoincrement number like so:

ALTER TABLE tbl AUTO_INCREMENT = 100;

Another remark: If you have numbers one to ten, and you remove 5, you cannot easily do this. You can hardly make the next auto_increment 5 because 6 is already there.

So again, while you can do something dirty for your example, it's really hard to do this in a real environment. Maybe start a new question with description of your situation, and ask for advice how to approach that problem without the auto_increment tricks :)