sql - 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 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'