Difference between truncate and delete

Can anyone tell me the difference between Truncate and deleting a table?

 

Basically, truncate will remove all data from a table, same as deleting all rows but quicker.

Delete can be used to selectively delete certain rows, and the action can be rolled back (reversed). Truncate removes all rows and cannot be rolled back. It is much quicker than delete but it all depends on what your exact requirements are...

cheers
wootty

 

A Table has structure and data.

Truncate deletes the data but leaves the structure intact. Table still exists
but with no data.

Deleting a table results in deletion of both structure and data. Table will cease to exist.

 

I believe you're thinking about drop, not delete.

With delete you can remove all rows of data (or just a few) but new inserts doesn't start on ID 1.

Truncate also removes all rows of data but also resets auto_increments

Both variants keeps the table structure intact.

For example you have this data:

ID | name
-----------
1  | abc
2  | qwerty
3  | asdf

Code (markup):

If you 'delete' everything and add something new it will continue on ID 4
If you 'truncate' and add something it will start on ID 1

(Bit tired so might have messed something up )

If you're running a very old version of mysql the truncate command is linked to the delete command, so then you have to use an extra command to set the auto_increment to 1 (Even though you better update mysql instead! )

 

truncate is basically A DDL (data definition language ) whereas delete is DML( data manipulation language) in nature. delete commands removes one row/tuple at a time(hence reports the number of rows deleted from the table) while truncate wipes off the whole table and recreates it with no data,
hence truncate works much faster than delete command. Truncate does not provide any such report as number of rows deleted.

 

DELETE:

1. The mysql command 'DELETE' is used to delete space allocated by mysql server and the structure of table remains same.
2. It is removed rows.
3. It can be used by either 'WHERE' clause or without it in mysql.
4. The data can be roll backed, if it is removed by 'DELETE' mysql command. If the transaction is used which is yet committed before delete the row, then you can roll backed again.

TRUNCATE:
1. Remove rows from mysql table but the structure of table remains same.
2. The data cannot be roll backed if it is deleted by 'TRUNCATE' mysql command.
3. It can be used by only without 'WHERE' clause in mysql.

Reference: http://www.phponwebsites.com/2014/01/mysql-delete-truncate-drop-difference.html

 

By Delete command, you can delete the the data of the table, as well as, the table and after that if you want your table back you can roll back your action.By Truncate command, you can delete entire table but you can't roll back it.

 

As your question says..

Truncate a table means clear all the records in a table and deleting a table means deleting the structure of the table itself.

 

Eh, no. They do exactly the same - they delete records from the table - they don't do anything to the table-structure itself.
Where DELETE accepts WHERE-clauses, and hence can delete particular records, TRUNCATE removes ALL records in the given table.

To actually delete the table, you need to do a DROP-statement...

 

I will answer this Technically

Truncate
- when you use truncate all data is vanished with out being logged in your log file
- Auto_Increment value gets reset's when you use Truncate command
- Its a DDL command.
- Truncate can not be rollbacked until you use it in transactions

Delete
- Used to delete a specific record, using where condition
- Data is logged in log file, so can be inserted later if you want
- Delete is a DML command
- Delete doesn't effect Auto_Increment value

 

hey, this thread is 6 years old. I suppose thread starter already knows the difference

 

DELETE IS DML statement, LIMIT clause can also be used to set a limit on the number of rows to be deleted. deletes specified data if where condition exists. Slower than Truncate as it keeps logs, Rollback is possible.

Truncate is DDL Statement, Truncate drops the table and re-create it. It is much faster than deleting rows one by one. Cannot use where condition, it removes all data. Rollback is possible.