MySQL Delete all rows from table and reset ID to zero

Question

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

User · Accepted Answer

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

User · Answer

An interesting fact   I was sure TRUNCATE will always perform better  but in my case  for a database with approximately 30 tables with foreign keys  populated with only a few rows  it took about 12 seconds to TRUNCATE all tables  as opposed to only a few hundred milliseconds to DELETE the rows  Setting the auto increment adds about a second in total  but it s still a lot better   So I would suggest try both  see which works faster for your case

User · Answer

if you want to use truncate use this   SET FOREIGN KEY CHECKS   0   TRUNCATE table  table name   SET FOREIGN KEY CHECKS   1

User · Answer

If you cannot use TRUNCATE  e g  because of foreign key constraints  you can use an alter table after deleting all rows to restart the auto increment   ALTER TABLE mytable AUTO INCREMENT   1

User · Answer

If table has foreign keys then I always use following code   SET FOREIGN KEY CHECKS   0  -- disable a foreign keys check SET AUTOCOMMIT   0  -- disable autocommit START TRANSACTION  -- begin transaction     DELETE FROM table name  ALTER TABLE table name AUTO INCREMENT   1  -- or TRUNCATE table name  -- or DROP TABLE table name  CREATE TABLE table name              SET FOREIGN KEY CHECKS   1  -- enable a foreign keys check COMMIT   -- make a commit SET AUTOCOMMIT   1     But difference will be in execution time  Look at above Sorin s answer

[mysql] MySQL Delete all rows from table and reset ID to zero

Examples related to mysql