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

In MySQL, the TRUNCATE TABLE statement allows you to delete all rows from a table.

Here’s the basic syntax of the TRUNCATE TABLE statement:

TRUNCATE [TABLE] table_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the table from which you want to delete all rows after the TRUNCATE TABLE keywords.

The TABLE 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 that reference the table that you truncate, the TRUNCATE TABLE statement will fail.

Since a truncate operation causes an implicit commit, it cannot be rolled back. Also, the TRUNCATE TABLE statement does not fire DELETE triggers associated with the table that is being truncated.

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

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

Functionally, the TRUNCATE TABLE statement is like a DELETE statement without a WHERE clause that deletes all rows from a table:

DELETE FROM table_name;

Or a sequence of DROP TABLE and CREATE TABLE statements:

DROP TABLE table_name;

CREATE TABLE table_name (
  /* ... */
);Code language: SQL (Structured Query Language) (sql)

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

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;Code language: SQL (Structured Query Language) (sql)

Next, insert dummy data into 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 ;Code language: SQL (Structured Query Language) (sql)

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

CALL load_book_data(10000);Code language: SQL (Structured Query Language) (sql)

After that, check the data in the books table:

SELECT * FROM books;Code language: SQL (Structured Query Language) (sql)

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

TRUNCATE TABLE books;Code language: SQL (Structured Query Language) (sql)

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

Summary

• Use the TRUNCATE TABLE statement to delete all rows from a table efficiently.
• The TRUNCATE TABLE statement resets the AUTO_INCREMENT counter.