Updated May 30, 2023
Introduction to TRUNCATE TABLE MySQL
In MySQL, the truncate table command deletes all the rows present in a particular database table. The truncate table command performs the same job as the delete command. The delete command is used to delete table records or a sequence of drop tables and create table commands when considering the output logically. But, what the truncate command does internally is that it drops the table and recreates it; After successfully executing the truncate command, it returns the result of zero rows affected, unlike the delete statement, which 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 the truncate command and learn its syntax and usage with the help of an example.
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 you wish to truncate, i.e., whose records are to delete, keeping the table structure intact. The use of the TABLE keyword is optional in syntax. However, it is a good practice to use the TABLe keyword to avoid confusion between the TRUNCATE command statement and the TRUNCATE() function.
The execution of the above statement will result in the deletion of all the records in the table, and the action cannot be rolled back once the truncation is performed. Hence, one needs to be careful while using this command. If the table named name_of_the_table has any foreign key references, the execution of the command will fail.
Working of TRUNCATE TABLE Statement
The TRUNCATE statement is considered a DDL (Data Definition Language) command, not a DML (Data Manipulation Command). We consider it as such because when we use this command, we do not manipulate the table contents. Instead, the TRUNCATE statement drops and recreates the entire table. Before using the TRUNCATE statement, there are specific points that we should be aware of, which are as follows:
- Executing a TRUNCATE command on a table does not trigger the defined delete event because the command does not delete rows individually. Instead, it deletes the entire table in a single operation.
- Rolling back the action of the TRUNCATE TABLE command is not possible because it is implicitly committed. However, if any problem occurs while truncating, MySQL rolls back the whole action, keeping the data intact as previously.
- If any activation locks exist on the table, the TRUNCATE TABLE command cannot be executed until and unless the lock is unlocked.
- If any other tables have defined foreign keys that reference the columns of the current table, the current table cannot be truncated. This is because the foreign key constraints in those tables depend on the columns of this table.
- The execution of the TRUNCATE query always results in 0 rows affected, which does not provide information about the number of records deleted. Instead, it stands for no information.
- Truncating a table deletes all the indexes and data within the table. However, it’s important to note that any partitions defined on the table will remain intact even after the truncation process.
- The column assigned the AUTO_INCREMENT attribute resets its associated sequence to the beginning value. This is also valid for the storage engines like InnoDB and MyISAM that do not support reusing sequence values.
- We can even truncate a corrupted InnoDB table 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.
Example
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 values –
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 provides 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 –
When executing the truncate command, the query output may display 0 rows affected, even though it deletes all the records in the table. 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 the 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 references 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 by some other table. Hence, we will not be able to truncate the table educba_writers anymore.
TRUNCATE TABLE educba_writers;
Conclusion
In MySQL, we use the TRUNCATE command to efficiently and quickly delete all the rows of a table. We can use this command, but we must keep certain things in mind before using it about the behavior of the statement we discussed previously.
Recommended Articles
We hope that this EDUCBA information on “TRUNCATE TABLE MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
