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.

score 346 · Accepted Answer · edited Mar 03 '16 at 07:33

346

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.

edited Mar 03 '16 at 07:33

Sanghyun Lee

21,644
19
100
126

answered Sep 29 '12 at 10:20

Francois

10,730
7
47
80

22

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](http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table) – julian soro May 16 '14 at 23:41
2

do remember that truncate table is not going to rollback – penny chan Oct 26 '17 at 06:42
A quick one and it worked, where I had to clear the existing rows and reset the auto increment primary key column. Thanks. – Rajkumar M Jul 01 '21 at 05:02

score 99 · Answer 2 · answered Sep 29 '12 at 10:47

99

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

answered Sep 29 '12 at 10:47

3

@NBhargav Because you might be using InnoDB engine on your table instead of MyISAM, the first don't support resetting the index. – Gustavo Rubio Feb 13 '14 at 01:11
how to delete all the rows before altering the auto_increment value – Kasun Siyambalapitiya Jul 31 '16 at 17:10
@KasunSiyambalapitiya `DELETE FROM tablename;` (but that isn't going to work well when there are FK constraints -- see http://stackoverflow.com/a/5452798/507761) – Matthew Read Jan 24 '17 at 16:23

score 18 · Answer 3 · edited Jul 26 '16 at 08:45

18

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.

edited Jul 26 '16 at 08:45

brianlmerritt

2,644
1
34
39

answered Oct 13 '15 at 18:31

blacky

209
2
5

3

This is a good way to have orphan data in any table that foreign keys to the table you're wiping out. Enabling the foreign key checks after the fact does not cause MySQL to revalidate those foreign keys as far as I am aware. This leaves you with rows that contain data that *does not exist* in the reference table. You may as well not even *have* foreign keys on your table at all. – cimmanon Dec 29 '15 at 15:50

score 10 · Answer 4 · answered Mar 20 '20 at 19:46

10

if you want to use truncate use this:

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

answered Mar 20 '20 at 19:46

david2020

429
1
4
14

1

For testing and debugging a database, this is TOTALLY the answer. – Alan Stewart Oct 24 '20 at 04:02

score 8 · Answer 5 · edited Jul 31 '16 at 17:51

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.

MySQL Delete all rows from table and reset ID to zero

5 Answers5

Linked