The structured query language is almost the same for MySQL, SQL Server, or other database management systems. Most of the commands are the same and used for the same purpose. In this post, we are going to see the difference between the truncate and delete command in MySQL.
The DELETE command is used to delete a row from a table. It is a DML command that stands for Data Manipulation Language. The syntax for using this command is as follows:
DELETE FROM table_name;
You can delete the records based on a condition by using the WHERE clause:
DELETE FROM table_name
If you do not specify any condition, the DELETE command removes all the records from the table.
The TRUNCATE command is used to delete all the records from a table. It is a DDL command which means Data Definition Language. This command empties a table and no data is left behind. Unlike the DELETE command, you cannot specify a condition to delete the records. The syntax for using this method is as follows:
TRUNCATE TABLE Table_name;
The data deleted by the TRUNCATE command cannot be rolled back in any way.
Difference between DELETE and TRUNCATE Command:
- The DELETE command is used to delete particular rows and records based on a condition whereas the TRUNCATE command is used to delete all the records from a table.
- The DELETE is a DML command and TRUNCATE is a DDL command.
- DELETE command is slower than the TRUNCATE command.
- DELETE command locks a tuple before removing it whereas the TRUNCATE command locks the data page before removing data.
- To delete records using the DELETE command, we need DELETE permission whereas to use the TRUNCATE command, we need ALTER permission.
- DELETE command can be used with indexed views whereas TRUNCATE could not be used with indexed views.