TRUNCATE TABLE MySQL

Introduction to TRUNCATE TABLE MySQL

Truncate table command is used in MySQL to delete all the rows present in the particular table of the database. Truncate table does the equal job as that of delete command which is used for deleting the records of the table or sequence of drop table and create table command when considered the output logically. But, what truncate command does internally is that it drops the table and recreates it hence the result returned after successful completion of execution of the truncate command is zero rows affected unline the delete statement that returns the number of rows that were deleted. The zero number of rows affected stands for no information as the output. In this article, we will learn about the working and internals of truncate command and learn its syntax and usage with the help of an example.

Working of TRUNCATE TABLE Statement

Truncate statement is considered as the DDL(Data Definition Language) command and not the DML(Data Manipulation Command). This is because whenever we use this command the table contents are not manipulated instead the whole table is dropped and recreated. Because of this, there are certain points that we should know before using the TRUNCATE statement which is listed below –

• Whenever a TRUNCATE command is executed on the table the trigger defined on delete event is not executed as rows are not deleted the whole table is deleted.
• We cannot rollback the action of the TRUNCATE TABLE command as it is implicitly committed. However, if any problem occurs while truncating the whole action is rollbacked by MySQL keeping the data intact as previous.
• If there are any of the activation locks on the table then TRUNCATE TABLE command cannot be executed on it until and unless that lock is unlocked.
• If there is any of the foreign key defined in other tables on the current table then the current table cannot be truncated as other table’s foreign key is referencing the columns of this table.
• The output of the execution of the TRUNCATE query is always 0 rows affected which do not specify the number of records deleted instead it stands for no information.
• All the indexes and data of the table are deleted but the partitions defined on the table remain intact even after truncating the table.
• The sequence associated with the column which was assigned the AUTO_INCREMENT attribute is reset to the beginning value. This is also valid for the storage engines like InnoDB and MyISAM that do not support reusing of sequence values.
• We can even truncate an InnoDB table that is corrupted using the TRUNCATE statement.
• Considering the performance of the TRUNCATE command, it always exceeds the DELETE command performed for all the rows because it skips the DML operations involved in the table manipulation.

Syntax:

The syntax of the TRUNCATE command is as follows –

TRUNCATE [TABLE] name_of_the_table;

The name_of_the_table is the table name that you wish to truncate i.e whose records are to delete keeping the structure of the table intact. The use of the TABLE keyword is optional in syntax. However, it is a good practice to use the TABLe keyword to avoid the confusion between the TRUNCATE command statement and TRUNCATE() function.

The execution of the above statement will lead to the deletion of all the records present in the table and once truncated the action cannot be rollbacked. Hence, one needs to be careful while using this command. If there is any foreign key reference on the table named name_of_the_table then the execution of the command will fail.

Let us consider one example, We will create one table named educba_writers using the following CREATE TABLE command –

CREATE TABLE `educba_writers` (
`id` int(11) NOT NULL,
`firstName` varchar(10) COLLATE latin1_danish_ci NOT NULL,
`rate` decimal(5,2) DEFAULT NULL,
`joining_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

that gives the following output –

Let us insert some more rows with rate and joining date value –

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date`) VALUES
(1, 'Payal', 750, "2020-05-01"),
(2, 'Vyankatesh', 700, "2020-01-01"),
(3, 'Omprakash', 600, "2020-02-01"),
(4, 'sakshi', 200, "2020-06-01"),
(5, 'prerna', 150, "2020-02-01"),
(6, 'preeti', 350, "2020-06-01"),
(7, 'sanjana', 400, "2020-02-01"),
(8, 'omkar', 450, "2020-06-01"),
(9, 'sohail', 650, "2020-02-01"),
(10, 'soniya', 850, "2020-06-01"),
(11, 'supriya', 700, "2020-02-01"),
(12, 'saniya', 750, "2020-06-01"),
(13, 'omkar', 410, "2020-02-01"),
(14, 'akshay', 910, "2020-06-01"),
(15, 'akash', 730, "2020-02-01"),
(16, 'siddharth', 980, "2020-06-01");

that gives the following output –

Let us retrieve the records of the table educba_writers and confirm the records that exist in the table using the following SELECT query statement –

select * from educba_writers;

that gives the following output after execution –

Let us now truncate the table educba_writers using the TRUNCATE syntax discussed above and the following query statement –

TRUNCATE TABLE educba_writers;

that gives the following output –

As we can see the output of the query execution of truncate command returns that 0 rows are affected even when all the records present in that table are deleted. Let us confirm the deletion of the records of the table by selecting the records of the educba_writers table again using the following query statement –

select * from educba_writers;

that now gives the following output after execution with none of the records present in it –

Hence, we can see that the truncate command worked properly.

Let us insert some records in educba_writers table again –

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date`) VALUES
(1, 'Payal', 750, "2020-05-01"),
(2, 'Vyankatesh', 700, "2020-01-01"),
(3, 'Omprakash', 600, "2020-02-01"),
(4, 'sakshi', 200, "2020-06-01"),
(5, 'prerna', 150, "2020-02-01");

ALTER TABLE educba_writers ADD PRIMARY KEY (id)

Now, we will create one more table that will contain foreign key reference on the educba_writers table named books using the below CREATE TABLE query statement –

CREATE TABLE books (
bookId int NOT NULL,
name VARCHAR(100),
rate int NOT NULL,
writerId int,
PRIMARY KEY (bookId),
FOREIGN KEY (bookId) REFERENCES educba_writers(id)
);

and insert the following record in it –

INSERT INTO `books` (`bookId`, `name`, `rate`, `writerId`) VALUES
(1, 'Techniques Of MySQL', 750, 1),
(2, 'Concepts of OOPS', 700, 3);

Now, we will try truncating the same educba_writers table that we did previously. But now the table has foreign constraints referenced on it by some other table. Hence, we will not be able to truncate the table educba_writers anymore.

TRUNCATE TABLE educba_writers;

Conclusion

The TRUNCATE command is used in MySQL to delete all the rows of the table in an efficient and faster manner. We can use this command but we need to keep certain things in mind before using it about the behavior of the statement that we discussed previously.

This is a guide to TRUNCATE TABLE MySQL. Here we discuss the introduction, syntax, Working of TRUNCATE TABLE Statement respectively. You may also have a look at the following articles to learn more –

1. MySQL Data Type
2. MySQL INSERT IGNORE
3. MySQL Create Function
4. MySQL Lock Table