TRUNCATE TABLE Statement in MySQL

TRUNCATE TABLE statement empties a table completely. Logically, it is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements.

Syntax

Following is the syntax of TRUNCATE TABLE statement:

TRUNCATE [TABLE] table_name;

It will delete all data in a table table_name. The TABLE keyword is optional. If there is any FOREIGN KEY constraints from other tables which reference the truncate table, above statement will fail.

Example

Following example shows usage of TRUNCATE statement on table employleinfo.

# Create Table
CREATE TABLE employleinfo(id int, firstName varchar(50), lastName varchar(50), age int, city varchar(50));

# Insert record into the table
INSERT into employleinfo values(1,   "Rahul", "Bhagwat",20,"Noida");
INSERT into employleinfo values(2,   "Stephen",   "Fleming",   17,  "Delhi");
INSERT into employleinfo values(3,   "Sebastian", "Smith",     19,  "Patna");
INSERT into employleinfo values(4,   "Anjali",    "Sharma",    50,  "Mumbai");
INSERT into employleinfo values(5,   "Erica",     "Edwards",   40,  "Chennai");

# Empty Table
TRUNCATE TABLE employleinfo;

# Select from Table
SELECT * FROM employleinfo;

DELETE vs TRUNCATE

• Unlike a DELETE statement, the number of rows affected by the TRUNCATE TABLE statement is 0.
• TRUNCATE TABLE is more efficient than the DELETE statement. Because TRUNCATE drops and recreates the table instead of deleting rows one by one.
• DELETE statement can delete records that have foreign key constraints defined. A TRUNCATE cannot be executed if these same constraints are in place.