In MySQL, DELETE and TRUNCATE both are used for deleting data from table.

DELETE

• DELETE comes under DML(Data Manipulation Language).
• DELETE can be used to delete a particular row by using WHERE clause.
• It is slower than TRUNCATE as it keeps logs.
• Rollback is possible when used with TRANSACTION.
• If there is a primary key with auto-incremt in the table then DELETE will not reset the counter (means PK for new record will start from the last counter)

If we want to delete a record from table then we use DELETE FROM statement.

Syntax:

Below statement deletes rows from table on the basis of where condition, we can define any condition in the WHERE clause:

copytext

DELETE FROM table_name [WHERE Clause]

DELETE FROM table_name [WHERE Clause]

If WHERE clause is not specified with the statement then all the records will be deleted from the given table.

Example:

We have a table with the following values.

copytext

user
id user_name country
.......................................
1 John Canada
2 Chris America
3 Joy London
4 Jenny Korea 

user
id user_name country
.......................................
1 John Canada
2 Chris America
3 Joy London
4 Jenny Korea 

DELETE Examples

Delete the user where id is 4:

copytext

DELETE FROM user where id = 4;

DELETE FROM user where id = 4;

Result

copytext

user
id user_name country
.......................................
1 John Canada
2 Chris America
3 Joy London

user
id user_name country
.......................................
1 John Canada
2 Chris America
3 Joy London

Below statement deletes all the records from the table:

copytext

DELETE FROM table_name;

DELETE FROM table_name;

Example:

copytext

DELETE FROM user;

DELETE FROM user;

TRUNCATE

• TRUNCATE comes under DDL(Data Definition Language).
• Cannot use WHERE clause.
• It is faster than DELETE as it does not keep logs.
• Rollback is possible when used with TRANSACTION.
• If there is a primary key with auto-incremt in the table then TRUNCATE will reset the counter (means PK for new record will start from 1)

TRUNCATE TABLE statement is usually used to delete all the records inside a table.

The TRUNCATE TABLE Statement

When we want to delete all the records inside a table then, use the TRUNCATE TABLE statement.

Syntax:

copytext

TRUNCATE TABLE table_name;

TRUNCATE TABLE table_name;

Example:

The below statement deletes all the records of "user" table:

copytext

TRUNCATE TABLE user;

TRUNCATE TABLE user;

Hope this will help you :)