MySQL Delete all rows from table and reset ID to zero

http://stackoverflow.com/questions/12651867/mysql-delete-all-rows-from-table-and-reset-id-to-zero

up vote117down votefavorite 31	I need to delete all rows from a table but when I add a new row, I want the primary key ID, which has an auto increment, to start again from 0 respectively from 1. mysql shareimprove this question edited Sep 7 '15 at 20:20 Michael Currie 3,09921635 asked Sep 29 '12 at 10:18 marek_lani 1,02731529
	add a comment

4 Answers

activeoldestvotes

up vote198down voteaccepted	Do not delete, use truncate: Truncate table XXX The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values. Source. shareimprove this answer edited Mar 3 at 7:33 Hugh Lee 5,69175081 answered Sep 29 '12 at 10:20 Francois B. 6,02642654
	14   Truncate works well with non-constrained tables, but if your table has a Foreign Key constraint, you may consider using the Delete method. See this post if you have FK constraints: truncate foreign key constrained table – Julian Soro May 16 '14 at 23:41