Auto Increment After Delete in MySQL

Auto Increment after delete in MySQL

What you're trying to do sounds dangerous, as that's not the intended use of AUTO_INCREMENT.

If you really want to find the lowest unused key value, don't use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.

Take a step back and ask "why you need to recycle key values?" Do unsigned INT (or BIGINT) not provide a large enough key space?

Are you really going to have more than 18,446,744,073,709,551,615 unique records over the course of your application's lifetime?

how to auto increment with 1 after deleting data from table

Use this query while deleting your old 20 Records.

truncate table YourTableName;

It will reset the database structure and if you insert new record it will start from 1(one) id again.

auto increment after delete from a table

Look at this answer: Reorder / reset auto increment primary key

How to update auto-increment field after removing a row?

I think there is no option in MySQL to do this, but if you have a CMS and need to sorting auto-increment fields by MySQL, I know a better way that is fetching auto-increment fields value and put them in an array and then work with that array, for example This is your database table:

id  |  name  

and this is the value of your table:

25  | Ali
36  | Alex
37  | Reza
39  | Steven
40  | Mohammad

Then fetch this values and put them in array and then work for that from 0 to the end (count of fields on table):

for example You can have access to the each value what you want from 0 to end:

echo $array[0];

is it possible to using last id where it's deleted mysql?

How to achieve it?

To be honest, this is achieveable.Insert record's corresponding id value into an ordered queue every time it is deleted, and take the smallest value from the queue as the id to be insered(if the queue is empty, use id generated by database).

Is it worth doing?

I will assume that your primary key is the id, whcih is a clustered index in mysql. So this way may cause more page splits, which will slow insertion performance.

And you need to ensure that the id in the ordered queue can only be consumed by one inserting thread at the same time.If you inserted fail, you need to re-enqueue the id.If the size of the ordered queue is too large, the sorting operation will also slow performance.

At last, you can't rely on id for analysis. eg. The record with max id is the latest record.

---

Related Topics