MySQL is the world’s most popular open source database, recognized for its speed and reliability. This article looks at how to delete all the data from a MySQL database table and how it affects auto incremental fields.
Delete and Truncate
There are two ways to delete all the data in a MySQL database table.
TRUNCATE TABLE tablename; This will delete all data in the table very quickly. In MySQL the table is actually dropped and recreated, hence the speed of the query. The number of deleted rows for MyISAM tables returned is zero; for INNODB it returns the actual number deleted.
DELETE FROM tablename; This also deletes all the data in the table, but is not as quick as using the “TRUNCATE TABLE” method. In MySQL >= 4.0 the number of rows deleted is returned; in MySQL 3.23 the number returned is always zero.
Auto Increment Columns for MyISAM Tables
If you have an auto increment primary key column in your MyISAM table the result will be slightly different depending which delete method you use. When using the “TRUNCATE TABLE” method the auto increment seed value will be reset back to 1. When using the “DELETE FROM” method the auto increment seed will be left as it was before (eg if the auto increment field of last inserted record was 123 the next inserted record will be set to 124).
Note that this is true for MySQL >= 4.0; from my reading of the TRUNCATE manual page in MySQL 3.23 TRUNCATE works just like DELETE which would mean the auto increment seed is not reset. I do not currently have a 3.23 database set up to test it so cannot confirm this.
Auto Increment Columns for INNODB Tables
For INNODB tables, whether you use the “TRUNCATE TABLE” or “DELETE FROM” methods, the auto increment field will not be reset. If you inserted 5 records into a new table, then deleted all records and inserted another record, the field would have a value of 6, regardless of which method you used.
Update 17 Feb 2009: I originally wrote this post when MySQL 4.0 was the current version. I’ve just tested the above now on an INNODB table using MySQL 5.0 and using TRUNCATE does reset the auto increment field back to the default. So either the behaviour changed at some point or I was incorrect when making the above statement.
Are you really sure you want to delete all data?
Before deleting all the data in a database you should make sure you really intend to delete all the data. It often pays first to “SELECT * FROM tablename” or “SELECT COUNT(*) FROM tablename” before doing so to check that it really is safe to delete all data. Maybe you really want to do something like “DELETE FROM tablename WHERE foo = ‘bar'” instead.
