MySQL Delete all rows from table and reset ID to zero
MysqlMysql Problem Overview
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 Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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
Solution 3 - Mysql
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.
Solution 4 - Mysql
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.
Solution 5 - Mysql
if you want to use truncate use this:
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table $table_name;
SET FOREIGN_KEY_CHECKS = 1;