0

My requirement is to have a condition for the truncate query in MySQL since I want only to empty half of the table. I know I can use delete from table query. but the problem is. If I delete, the table contents the primary key (auto_increment) will continue from the id where it was before deleted. But truncate makes it start from the beginning.

If the answer is yes please tell me how, If No, can I know the alternative way?

Thanks in advance.

Honnavar Deepak
  • 11
  • 5
  • 1
    Short answer: no, truncate can only be used to empty the entire table. It does not use delete internally and is fast regardless of table size. Trunacate also resets the auto increment value which you can do yourself as well using alter table statement. – marekful Jul 26 '18 at 10:28
  • (InnoDB doesn't even reset AI after truncate). But you can select the highest existing number, and set the AI to number+1 ALTER TABLE table AUTO_INCREMENT = number+1; – kry Jul 26 '18 at 10:29
  • " (InnoDB doesn't even reset AI after truncate)" @kry Better read the manual "Any AUTO_INCREMENT value is **reset** to its **start value**. This is true even for MyISAM and **InnoDB**, which normally do not reuse sequence values. " source https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html – Raymond Nijland Jul 26 '18 at 10:49

0 Answers0