MySQL TRUNCATE TABLE

Created with Sketch.

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 statement allows you to delete all data in a table.

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

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.

Here is the basic syntax of theTRUNCATE TABLE statement:

TRUNCATE [TABLE] table_name;

Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the table which you want to remove all data 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 which reference the table that you truncate, the TRUNCATE TABLE 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 an AUTO_INCREMENT column.

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

Unlike a DELETE statement, the number of rows affected by the TRUNCATE TABLE 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;

Code language: SQL (Structured Query Language) (sql)

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 ;

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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *