MySQL TRUNCATE TABLE

 MySQL TRUNCATE TABLE

Summary: in this tutorial, you will learn how to use the MySQL TRUNCATE TABLE statement to delete all data in a table.

Introduction to the MySQL TRUNCATE TABLE statement

The MySQL TRUNCATE TABLE the statement allows you to delete all data in a table.

Logically, the TRUNCATE TABLE the statement is like a DELETE statement without a WHERE the clause that deletes all rows from a table, or a sequence of DROP TABLE and CREATE TABLE statements.

However, the TRUNCATE TABLE the statement is more efficient than the DELETE statement because it drops and recreates the table instead of deleting rows one by one.

Here is the basic syntax of theTRUNCATE TABLE statement:

TRUNCATE [TABLE] table_name;

In this syntax, you specify the name of the table which you want to remove all data after the TRUNCATE TABLE keywords.

The TABLE The keyword is optional. However, it is a good practice to use the TABLE keyword to distinguish between the TRUNCATE TABLE statement and the TRUNCATE() function.

If there is any FOREIGN KEY constraints from other tables which reference the table that you truncate, the TRUNCATE TABLE the statement will fail.

Because a truncate operation causes an implicit commit, therefore, it cannot be rolled back.

The TRUNCATE TABLE statement resets value in the AUTO_INCREMENT  column to its start value if the table has a AUTO_INCREMENT column.

The TRUNCATE TABLE the statement does not fire DELETE triggers associated with the table that is being truncated.

Unlike a DELETE the statement, the number of rows affected by the TRUNCATE TABLE the statement is 0, which should be interpreted as no information.

MySQL TRUNCATE TABLE example

Let’s take an example of using the TRUNCATE TABLE statement.

First, create a new table named books for the demonstration:

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;

Next, insert dummy data to the books table by using the following stored procedure:

DELIMITER $$
CREATE PROCEDURE load_book_data(IN num INT(4))
BEGIN
	DECLARE counter INT(4) DEFAULT 0;
	DECLARE book_title VARCHAR(255) DEFAULT '';

	WHILE counter < num DO
	  SET book_title = CONCAT('Book title #',counter);
	  SET counter = counter + 1;

	  INSERT INTO books(title)
	  VALUES(book_title);
	END WHILE;
END$$

DELIMITER ;

Then, load 10,000 rows into the books table. It will take a while.

CALL load_book_data(10000);

After that, check the data in the books table:

SELECT * FROM books;

Finally, use the TRUNCATE TABLE statement to delete all rows from the books table:

TRUNCATE TABLE books;

Note that you can compare the performance between the TRUNCATE TABLE with the DELETE statement.

In this tutorial, you have learned how to use the MySQL TRUNCATE TABLE statement to delete all data from a table efficiently, especially for a large table.