16

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.

gameover
  • 11,813
  • 16
  • 59
  • 70
  • Last query was to delete all rows. I made it explicit with a "where 1" clause. – gameover Jan 06 '10 at 13:05
  • Related post - [Difference between drop table and truncate table?](https://stackoverflow.com/q/135653/465053) – RBT Nov 22 '18 at 06:13

5 Answers5

28
  • 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'
nickf
  • 537,072
  • 198
  • 649
  • 721
  • The performance bug noted by @Nathan seems to have been addressed as of MySQL 8.0 (see the bottom of the page at [this link provided by Nathan](https://bugs.mysql.com/bug.php?id=68184)). You can also search the [index of 8.0 release notes](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ix01.html) for "truncate table" for more details. – blthayer Jul 04 '19 at 15:43
5
  • 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.

Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
5

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.

Community
  • 1
  • 1
php
  • 4,307
  • 1
  • 24
  • 13
2

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.

Roy Tang
  • 5,643
  • 9
  • 44
  • 74
2

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.
Gomzy
  • 431
  • 4
  • 14