Auto increment problem after delete in MYSQL

Question

I have a MYSQL table with field that has AUTO INCREMENT on . There are 2 records inserted when i'm trying to delete record 1 and 2 and insert new data id of that data should be start from 1 but it starting from 3 . Is there any solution ?

Answer 1

You are right !! I get it . Thank you for explanation...

Answer 2

Generally the point of auto increment, is to generate a unique sequential ID for each row. Why would you want to start again from 1?
What about the scenario when you insert 2 rows and only delete the 1st one. Then one row remains with ID 2. Would you prefer the next ID be 1 or 3? And how about the next one? 2 (although this already exists) or 4.

If you truncate the table and want to restart the sequence I think you can do this:
ALTER TABLE tbl AUTO_INCREMENT = 1;

Check more in the mysql reference
https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

Answer 3

Do NOT USE AUTO INCREMENT !🤗

Answer 4

Sanjay Kamath thank you..

Answer 5

Yes! There is a solution that you can reset. It can start from where you want.

QUERY IS - > ALTER TABLE table_name AUTO_INCREMENT = 5 //Optional.

5 refers last number of the table. Which means your auto incremental value should be grater than (5 or more) last number of the table.

If it lesser you can't have same ID in a table. Because it is auto increament. Keep remember it only works when you deleting records in last of the table not in the middle