+ 11

Auto increment problem after delete in MYSQL

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 ?

22nd Jul 2019, 2:35 PM
Ayushi Gujarati
Ayushi Gujarati - avatar
5 odpowiedzi
+ 4
You are right !! I get it . Thank you for explanation...
22nd Jul 2019, 2:58 PM
Ayushi Gujarati
Ayushi Gujarati - avatar
+ 3
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
22nd Jul 2019, 2:55 PM
Tibor Santa
Tibor Santa - avatar
+ 3
Do NOT USE AUTO INCREMENT !🤗
24th Jul 2019, 3:03 AM
Sanjay Kamath
Sanjay Kamath - avatar
+ 3
Sanjay Kamath thank you..
28th Jul 2019, 5:42 AM
Ayushi Gujarati
Ayushi Gujarati - avatar
+ 2
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
28th Jul 2019, 5:12 AM
Kalai Selvan
Kalai Selvan - avatar