TRUNCATE¶

You can delete all records in the specified table by using the TRUNCATE statement.

It has the following advantages over using the DELETE FROM [schema_name.]table_name statement without a WHERE clause.

It’s way faster due to deleting all indexes and constraints in advance and deleting records at once.
There is no vacuum cost.
It generates way lesser log records so that it is better in terms of HA replication, Recovery, and Rollback.

Note

The DELETE trigger is disabled while deleting records with the TRUNCATE statement.
The TRUNCATE statement initializes the AUTO INCREMENT column of the table. Therefore, if data is inserted, the AUTO INCREMENT column value increases from the initial value.
To execute the TRUNCATE statement, the authorization of ALTER, INDEX, and DELETE is required on the table. For granting authorization, see GRANT.
If a table is created with DONT_REUSE_OID option (DONT_REUSE_OID), other tables can refer to it as a column. When the specified table is referred to like this, it is impossible to truncate the table and it’s done with DELETE FROM statement internally.

TRUNCATE [ TABLE ] [schema_name.]table_name [ CASCADE ]

schema_name: Specifies the schema name. If omitted, the schema name of the current session is used.
table_name : Specifies the name of the table that contains the data to be deleted.
CASCADE : Deletes all records in all tables referring to the specified table with a foreign key. This is propagated to all tables in the foreign key relationship. A PRIMARY KEY constraint has to be defined in the table and this has to be referred to by one or more FOREIGN KEY, and the foreign key action has to be ON DELETE. It fails without this option when a foreign key referring to the specified table is defined. It also fails when even one ON DELETE action is not CASCADE in all foreign key relationships. See FOREIGN KEY Constraint for more information about the foreign key constraint.

CREATE TABLE a_tbl(A INT AUTO_INCREMENT(3,10) PRIMARY KEY);
INSERT INTO a_tbl VALUES (NULL),(NULL),(NULL);
SELECT * FROM a_tbl;

            a
=============
            3
            13
            23

--AUTO_INCREMENT column value increases from the initial value after truncating the table
TRUNCATE TABLE a_tbl;
INSERT INTO a_tbl VALUES (NULL);
SELECT * FROM a_tbl;

            a
=============
            3

The following example uses the CASCADE option. Note that even a record refers to nothing, but the propagated TRUNCATE deletes all records.

-- a_tbl <- b_tbl <- c_tbl
--       <- d_tbl
CREATE TABLE a_tbl(a1 INT PRIMARY KEY);
CREATE TABLE b_tbl(b1 INT PRIMARY KEY, b2 INT FOREIGN KEY REFERENCES a_tbl (a1) ON DELETE CASCADE);
CREATE TABLE c_tbl(c1 INT PRIMARY KEY, c2 INT FOREIGN KEY REFERENCES b_tbl (b1) ON DELETE CASCADE);
CREATE TABLE d_tbl(d1 INT PRIMARY KEY, d2 INT FOREIGN KEY REFERENCES a_tbl (a1) ON DELETE CASCADE);
INSERT INTO a_tbl VALUES (1);
INSERT INTO b_tbl VALUES (2, 1);
INSERT INTO c_tbl VALUES (3, 2);
INSERT INTO d_tbl VALUES (5, 1);
INSERT INTO d_tbl VALUES (4, NULL); -- not refer to any record in a_tbl, but will be deleted.
SELECT * FROM a_tbl;
SELECT * FROM b_tbl;
SELECT * FROM c_tbl;
SELECT * FROM d_tbl;

TRUNCATE a_tbl CASCADE;
SELECT * FROM a_tbl, b_tbl, c_tbl, d_tbl;

           a1
=============
            1

           b1           b2
==========================
            2            1

           c1           c2
==========================
            3            2

           d1           d2
==========================
            5            1
            4         NULL

-- after TRUNCATE a_tbl CASCADE;
There are no results.