Understanding MySQL TRUNCATE TABLE by Practical Examples

MySQL TRUNCATE TABLE statement is a DDL statement used for removing all records from the MySQL table. When we run this command, it drops the existing table first and then creates a new table using the table definition. The query execution sequence of the TRUNCATE TABLE command is DROP TABLE and CREATE TABLE.

The characteristics of the TRUNCATE TABLE statement are as follows:

1. When we run the TRUNCATE TABLE statement instead of deleting rows, MySQL drops the table and re-creates it. It is faster than the DELETE statement, particularly for large tables.
2. TRUNCATE TABLE bypasses the DELETE triggers to achieve high performance.
3. TRUNCATE TABLE cannot be rolled back because it causes an implicit commit.
4. If you are using atomic DDL-supported storage engines, and the server halts during the TRUNCATE TABLE operations, the transactions will be fully committed or rolled back.
5. It resets the AUTO_INCREAMENT columns values to the start value.
6. The TRUNCATE TABLE statement fails if you have created foreign keys on InnoDB or NDB tables.
7. Even though the table or index is corrupted, and the table definition is valid, the TRUNCATE TABLE statement recreates an empty table.
8. The TRUNCATE TABLE statement preserves partitioning. The index files and data will be dropped, but the partition definition won’t get affected.
9. You can truncate a corrupted InnoDB table.
10. If you are using file-per-table tablespace, the TRUNCATE TABLE statement drops the tablespace and creates a new one.
11. We can use TRUNCATE TABLE in performance schema summary tables, but it does not delete the tables’ data. It resets the summary column values to NULL or Zero (0).

Differences between DELETE and TRUNCATE Table Statements

Sr.	DELETE Statement	TRUNCATE TABLE Statement
1	DELETE statement is a DML statement. The changes are logged to the database binary logs.	TRUNCATE TABLE statement is a DDL Statement. The operation is not logged in the binary logs.
2	The operation can be rolled back.	This operation cannot be rolled back.
3	It is slower because changes are logged to the binary logs, DML triggers are executed.	It is faster because it drops the existing table and creates a new one.
4	DELETE statement acquires the row-level lock.	TRUNCATE TABLE locks the data pages before removing the data.
5	You can remove specific records by using the WHERE clause	Truncate table removes all data from the table.

Using the TRUNCATE TABLE Statement in Practice

The syntax of the Truncate Table statement is as follows:

TRUNCATE TABLE [DatabaseName].[TABLE_NAME]

Note: The name of the table you want to delete the record from is after the TRUNCATE TABLE statement. Suppose you want to truncate the tblStudent table. The query should be as follows:

TRUNCATE TABLE tblStudent

For the demonstration, I have installed MySQL 8.0 Server and created a database named studentsDB. There, I have created the tables named tblStudent, tblSchool, and tblStudentCounsiler.

The following script creates the database and tables:

Create database studentDB;

CREATE TABLE studentDB.tblstudent (
  studentID integer NOT NULL AUTO_INCREMENT,
  StudentName varchar(255) DEFAULT NULL,
  StudentGrade char(1) DEFAULT NULL,
  SchoolID int,
  PRIMARY KEY (studentID)
)
ENGINE = INNODB;

CREATE TABLE studentDB.tblschool (
  SchoolID int NOT NULL AUTO_INCREMENT,
  SchoolName varchar(255) DEFAULT '',
  City varchar(255) DEFAULT '',
  PRIMARY KEY (SchoolID)
)
ENGINE = INNODB;

CREATE TABLE studentDB.tblStudentCounsiler (
  CounsilerID int NOT NULL AUTO_INCREMENT,
  CounsilerName varchar(255) DEFAULT '',
  PRIMARY KEY (CounsilerID)
)
ENGINE = INNODB;

I have created a foreign key on the tblStudent table that references the SchoolID column of the tblStudent table.

The script to create a foreign key is the following:

ALTER TABLE studentDB.tblstudent
ADD CONSTRAINT FK_tblstudent_SchoolID FOREIGN KEY (SchoolID)
REFERENCES studentDB.tblschool (SchoolID) ON DELETE NO ACTION;

The ER diagram is below:

The following script inserts the summed data in all tables:

Use studentDB;

insert INTO tblSchool(SchoolName,City) VALUES ('Nalanda School','Mehsana');
insert INTO tblSchool(SchoolName,City) VALUES ('Sarvajanik School','Mehsana');
insert INTO tblSchool(SchoolName,City) VALUES ('Diwan Ballubhai','Ahmedabad');

INSERT INTO tblstudent (StudentName,StudentGrade,SchoolID) VALUES ('Nisarg Upadhyay','A',1);
INSERT INTO tblstudent (StudentName,StudentGrade,SchoolID) VALUES ('Nirali Upadhyay','A',2);
INSERT INTO tblstudent (StudentName,StudentGrade,SchoolID) VALUES ('Manushi Upadhyay','C',3);
INSERT INTO tblstudent (StudentName,StudentGrade,SchoolID) VALUES ('Dixit Upadhyay','B',1);
INSERT INTO tblstudent (StudentName,StudentGrade,SchoolID) VALUES ('Bharti Upadhyay','A',1);

INSERT INTO tblStudentCounsiler (CounsilerName) VALUES ('Raghav Dave');
INSERT INTO tblStudentCounsiler (CounsilerName) VALUES ('Keyur Dalwadi');
INSERT INTO tblStudentCounsiler (CounsilerName) VALUES ('Sawan Panchal');

Now let us understand the use cases.

First, let us truncate the tblStudentCounsiler with the following query:

mysql> truncate table studentdb.tblStudentCounsiler;

The output:

Query OK, 0 rows affected (0.08 sec)

Run the SELECT statement to view the data:

mysql> select * from studentdb.tblStudentCounsiler;

Query output:

Empty set (0.01 sec)

As you can see in the screenshot above, the command was executed successfully.            

Now, let us try to truncate the tblSchool table with the following query:

mysql> Truncate table tblSchool;

The following error occurs because we cannot truncate a table that is being referenced by another table:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`studentdb`.`tblstudent`, CONSTRAINT `FK_tblstudent_SchoolID`)

To fix this error, we must drop the foreign key. Run the following query to do it:

mysql> ALTER TABLE tblstudent DROP FOREIGN KEY FK_tblstudent_SchoolID;

Query Output:

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Once the foreign key is dropped, run the truncate table command:

mysql> Truncate table tblSchool;

Query Output:

Query OK, 0 rows affected (0.08 sec)

Summary

The current article introduced the TRUNCATE TABLE statement and its characteristics. We got familiar with the differences between TRUNCATE TABLE and the DELETE commands, as well as explored some practical examples to clarify the concepts.

Tags: mysql, truncate table Last modified: March 30, 2023