Mysql: Which to use when: drop table, truncate table, delete from table
mysql, sql
Solution
- `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'`
Problem
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.