MySQL TRUNCATE TABLE Tutorial and Examples

If you need to clear all rows in a table, you can use the DELETE * FROM table statement or the TRUNCATE TABLE statement.

The TRUNCATE TABLE statement is more efficient than the DELETE statement.

MySQL TRUNCATE TABLE syntax

The syntax for TRUNCATE TABLE is very simple, as follows:

TRUNCATE [TABLE] table_name;

The TABLE keyword is optional. That is, TRUNCATE t; is equivalent to TRUNCATE TABLE t;.

The TRUNCATE TABLE statement is equivalent to dropping the table first and then creating a new table. The TRUNCATE TABLE statement requires DROP permission.

TRUNCATE is different from DELETE

Although TRUNCATE is similar to DELETE, they differ in the following ways:

• The TRUNCATE is classified as a DDL statement, while the DELETE is classified as a DML statement.
• The TRUNCATE operation cannot be rolled back, but DELETE can be rolled back.
• The TRUNCATE operation drop and rebuild tables, which are much faster than DELETE.
• The TRUNCATE operation resets the auto-increment of the table, but DELETE does not.
• The TRUNCATE operation does not activate the delete trigger, but DELETE does.
• The TRUNCATE statement does not return a value representing the number of deleted rows, it normally returnes 0 rows affected. The DELETE statement returns the number of rows deleted.
• If a table is referenced by foreign keys of other tables, The TRUNCATE operation will fail.

TRUNCATE Examples

This example demonstrates the usage of the TRUNCATE TABLE statement.

First, we create a table name test for demonstration:

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  v INT
);

Then we use the following sql statement to insert 100,000 rows of data:

INSERT INTO test (v)
SELECT (
    t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i
  ) v
FROM (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t0,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t1,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t2,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t3,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t4
ORDER BY v;

Query OK, 100000 rows affected (1.24 sec)
Records: 100000  Duplicates: 0  Warnings: 0

Delete all rows in all tables with the following DELETE statement:

DELETE FROM test;

Query OK, 100000 rows affected (1.01 sec)

This is the return result of the DELETE statement. It explicitly tells us that 100,000 rows were deleted, taking a total of 1.01 seconds.

After re-inserting 100,000 rows using the above INSERT statement, empty the table with the following TRUNCATE TABLE statement:

TRUNCATE TABLE test;

Query OK, 0 rows affected (0.02 sec)

This is the return result of the TRUNCATE TABLE statement. It tells us that it took 0.02 seconds in total. Here it can be seen that TRUNCATE is much DELETE faster than.

Finally, we insert a row of data with the following statement:

INSERT INTO test (v) values (1);

Then look at the data in the table:

SELECT * FROM test;

+----+------+
| id | v    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

The output tells us that after the TRUNCATE operation, the id is reset to 1. This is not the same as DELETE action.

If you need to keep the original self-increment after deleting the data, please do not use TRUNCATE.

Conclusion

In this article, we described how to use the TRUNCATE TABLE statement. The main points of this article are as follows:

• The TRUNCATE TABLE statement is used to clear a table. The TABLE keyword can be omitted.
• TRUNCATE TABLE is equivalent to dropping and rebuilding the table, it cannot be rolled back.
• TRUNCATE TABLE is faster than DELETE, especially for large data tables.
• TRUNCATE TABLE will reset the auto increment value of the table.