Tuesday, June 19, 2012

MySQL: Delete All Data in a MySQL Table

Delete and Truncate


TRUNCATE TABLE tablename;
This will delete all data in the table very quickly.In  MyISAM table; When using the "TRUNCATE TABLE" method the auto increment seed value will be reset back to 1.

DELETE FROM tablename;
This also deletes all the data in the table, but is not as quick as using the "TRUNCATE TABLE" method.
In MyISAM table; When using the "DELETE FROM" method the auto increment seed will be left as it was before.

** For INNODB tables using MySQL 4.0, whether you use the "TRUNCATE TABLE" or "DELETE FROM" methods, the auto increment field will not be reset.
But, using MySQL 5.0, TRUNCATE does reset the auto increment field back to the default.

Count record in table

SELECT COUNT(*) FROM tablename;

** Check that it really is safe to delete all data Or just delete certain data instead all.


Delete data with condition

DELETE FROM tablename WHERE foo = 'bar'";




Drop Table From Database

use  database1;
DROP TABLE  tablename;


..
 

0 comments:

Post a Comment