Auto Increment Problem in Mysql

I created a table and set a field to auto increment some thing like this:

CREATE TABLE t1(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE = MyISAM AUTO_INCREMENT = 123456; 

But to some reason i de开发者_Python百科leted some of the rows in the table.

Now the question is when i insert new rows in the table the new rows should be assigned id's of the rows which have been deleted rather than assigning new id's.

I do not want to reset all the id's

How can i do this??

Help appreciated:)

---

Sorry to say, but that is not the use of AUTO_INCREMENT. If you want to re-use id's, then you would have to write your own trigger functions, and doing this is generally considered bad practice.

Imagine you were on id 50,000, and deleted an entry with id 1... would you really want the next record you add to re-use id 1?

---

The whole point of AUTO_INCREMENT is to auto increment...

You can explicitly assign these ids though and mysql will allow it.

---

You are going to have to do this manually rather than rely on MySQL to do it for you. The AUTO-INCREMENT flag keeps an integer that is incremented upon every insert statement and is assigned as the PK of the subsequent insert. Unless you want to write an update trigger that resets this value to the lowest non-used integer, I would suggest processing this in a server-side scripting language.

In any case, though, why is using the auto increment value a problem?

---

To reset the autoincrement value, you can use

ALTER TABLE t1 AUTO_INCREMENT=1

The next inserted record will use ID 1.

---

This might be something you're after.

alter table Users AUTO_INCREMENT=0;

This will reset the auto_increment back to 0 + whatever the current highest id is.

if you have 30, your next entry would be 31