Mysql: Which to use when: drop table, truncate table, delete from table.

Question

List the differences between the following MySql commands.

• drop table tablename;
• truncate table tablename;
• delete from tablename where 1;

Also from your experiences please tell me typical usage scenario for each.

Answer 1

• Drop is deleting the table. I would drop the table if I didn't need it anymore
• "Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables." - from MySQL manual
• I would use delete to do a delete (of specific rows) with a query.

I rarely "delete" anything from my databases. I generally put a flag column such as deleted as a boolean value. I check for that. If its true, I don't present that data.

Answer 2

• drop table tablename;
  • After this, it's gone. No more table. No more data.
  • Use this when you don't need that table any more.
• truncate table tablename;
  • After this, the table is empty, and (importantly) auto-incrementing keys are reset to 1. It's quite literally like having a brand new table.
  • Use this when you just want an empty table. It's faster than DELETE because it simply deletes all data. DELETE will scan the table to generate a count of rows that were affected.
• delete from tablename;
  • This lets you filter which rows to delete based on an optional WHERE clause.
  • Use this when you want to delete specific records, eg: DELETE FROM tablename WHERE username = 'joe'

Answer 3

In addition to the answers above, on Oracle RDBMS, "delete" is a transaction that can be rolled back if you didn't commit. "Truncate" cannot.