CODEWITHSUNDEEP
sqlmysql
gameover
gameover

Reputation: 12013

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

List the differences between the following MySql commands.

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

Upvotes: 17

Views: 16256

Answers (5)

Gomzy
Gomzy

Reputation: 431

Delete

  • DELETE statement deletes table rows and returns number of rows deleted

Truncate

  • TRUNCATE drops the table and re-create it. It is much faster than deleting rows one by one.
  • TRUNCATE removes all the rows from the Table.
  • TRUNCATE does not return number of deleted rows.

Drop

  • deletes the data as well as structure.
  • The difference between DROP and DELETE table is that, after executing DELETE statement the contents of table are removed but the structure remains same, but in case of DROP statement both the contents and structure are removed.

Upvotes: 2

php
php

Reputation: 4425

Delete

  • delete rows only
  • Mysql keeps its definition
  • You've chance to get roll back

Truncate:

  • It is same as delete but you can roll back data again

Drop

  • delete table data and structure of data from mysql database
  • can't roll backed again

If you want to delete only rows, then you can use DELETE
If you want to delete table from database, then you can use DROP

Before delete data, think well whether data is useful or not. Because you can't get again that data.

Upvotes: 5

Roy Tang
Roy Tang

Reputation: 5761

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.

Upvotes: 2

nickf
nickf

Reputation: 546035

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

Upvotes: 28

Daniel A. White
Daniel A. White

Reputation: 190945

  • 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.

Upvotes: 5

Related Questions