TRUNCATE

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

This statement internally delete first all indexes and constraints defined in a table and then deletes all records. Therefore, it performs the job faster than using the DELETE FROM table_name statement without a WHERE clause.

If the PRIMARY KEY constraint is defined in the table and this is referred by one or more FOREIGN KEY, it follows the FOREIGN KEY ACTION. If the ON DELETE action of FOREIGN KEY is RESTRICT or NO_ACTION, the TRUNCATE statement returns an error. If it is CASCADE, it deletes FOREIGN KEY. 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.

Note

To execute the TRUNCATE statement, the authorization of ALTER, INDEX, and DELETE is required on the table. For granting authorization, see GRANT.

TRUNCATE [ TABLE ] <table_name>

• table_name : Specifies the name of the table that contains the data to be deleted.

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