0

In my table there is column name id which is auto increment integer. When I truncate table and then try to perform insert query it starts from 1, but if I do delete operation and then try to insert then it just resumes from previous value. Why does insert query performs differently for delete and truncate. It should always start from 1 if there is not entry in table.

Example : (1) Original Table enter image description here

(2) delete all rows and then insert enter image description here

(3) truncate the table then insert enter image description here

WeAreRight
  • 885
  • 9
  • 24
  • FYI......whenever you truncate its reset the identity while in case of delete operation its start from last value of id+1 – Ankit Agrawal Feb 22 '17 at 11:15
  • i have mentioned upper that is the another main difference between delete and truncate – Ankit Agrawal Feb 22 '17 at 11:15
  • This is expected behaviour see https://dev.mysql.com/doc/refman/5.7/en/delete.html under the heading auto-increment columns. If you need to reset after deleting everything use alter table or drop and create table. – P.Salmon Feb 22 '17 at 11:18

3 Answers3

1

The current max value for the autoincrement function is stored in the table definition (you can see it when you run show create table idle. When you delete all rows from the table, the autoincrement value will stay the same. When you truncate the table you basically drop the table and recreate it, which resets the the autoincrement value to 0.

Hope this helps.

Jan Zeiseweis
  • 3,718
  • 2
  • 17
  • 24
1

I think you are about to reset auto increment, run this query after delete query:

 ALTER TABLE tablename AUTO_INCREMENT = 1 ;

NOTE: be careful about duplicate keys, if you delete some ids and reset ids to 1, while id=2 or n exists!

Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

some other actions and notes mentioned in this topic

Community
  • 1
  • 1
MohaMad
  • 2,575
  • 2
  • 14
  • 26
0

you give AUTO INCREMENT in id that's why it is behave like this.whenever you truncate its reset the identity while in case of delete operation its start from last value of your id. if don't want this behavior remove AUTO INCREMENT index from id field.

Kishan Oza
  • 1,707
  • 1
  • 16
  • 38