# TRUNCATE TABLE

## Syntax

```sql
TRUNCATE [TABLE] tbl_name
  [WAIT n | NOWAIT]
```

## Description

`TRUNCATE TABLE` empties a table completely. It requires the `DROP` privilege. See [GRANT](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant).

`tbl_name` can also be specified in the form `db_name`.`tbl_name` (see [Identifier Qualifiers](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-qualifiers)).

Logically, `TRUNCATE TABLE` is equivalent to a [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete) statement that deletes all rows, but there are practical differences under some circumstances.

`TRUNCATE TABLE` will fail for an [InnoDB table](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) if any FOREIGN KEY constraints from other tables reference the table, returning the error:

```sql
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
```

Foreign Key constraints between columns in the same table are permitted.

For an InnoDB table, if there are no `FOREIGN KEY` constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one. The [AUTO\_INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment) counter is reset by `TRUNCATE TABLE`, regardless of whether there is a `FOREIGN KEY` constraint.

The count of rows affected by `TRUNCATE TABLE` is accurate only when it is mapped to a `DELETE` statement.

For other storage engines, `TRUNCATE TABLE` differs from`DELETE` in the following ways:

* Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
* Truncate operations cause an implicit commit.
* Truncation operations cannot be performed if the session holds an active table lock.
* Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is "0 rows affected," which should be interpreted as "no information."
* As long as the table format file `tbl_name.frm` is valid, the table can be re-created as an empty table with `TRUNCATE TABLE`, even if the data or index files have become corrupted.
* The table handler does not remember the last used [AUTO\_INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment) value, but starts counting\
  from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.
* When used with partitioned tables, `TRUNCATE TABLE` preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.
* Since truncation of a table does not make any use of `DELETE`, the `TRUNCATE` statement does not invoke `ON DELETE` triggers.
* `TRUNCATE TABLE` will only reset the values in the [Performance Schema summary tables](https://mariadb.com/docs/server/reference/system-tables/performance-schema/performance-schema-tables/list-of-performance-schema-tables) to zero or null, and will not remove the rows.

For the purposes of binary logging and [replication](https://mariadb.com/docs/server/server-usage/storage-engines/myrocks/myrocks-and-replication), `TRUNCATE TABLE` is treated as [DROP TABLE](https://mariadb.com/docs/server/server-usage/tables/drop-table) followed by [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) (DDL rather than DML).

`TRUNCATE TABLE` does not work on [views](https://mariadb.com/docs/server/server-usage/views). Currently, `TRUNCATE TABLE` drops all historical records from a [system-versioned table](https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables).

#### WAIT/NOWAIT

Set the lock wait timeout. See [WAIT and NOWAIT](https://mariadb.com/docs/server/reference/sql-statements/transactions/wait-and-nowait).

### Oracle-mode

[Oracle-mode](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle) permits the optional keywords `REUSE STORAGE` or `DROP STORAGE` to be used.

```sql
TRUNCATE [TABLE] tbl_name [{DROP | REUSE} STORAGE] [WAIT n | NOWAIT]
```

These have no effect on the operation.

### Performance

`TRUNCATE TABLE` is faster than [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete), because it drops and re-creates a table.

With [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb), `TRUNCATE TABLE` is slower if [innodb\_file\_per\_table=ON](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_file_per_table) is set (the default). This is because `TRUNCATE TABLE` unlinks the underlying tablespace file, which can be an expensive operation. See [MDEV-8069](https://jira.mariadb.org/browse/MDEV-8069) for more details.

The performance issues with [innodb\_file\_per\_table=ON](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_file_per_table) can be exacerbated in cases where the [InnoDB buffer pool](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-buffer-pool) is very large and [innodb\_adaptive\_hash\_index=ON](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_adaptive_hash_index) is set. In that case, using [DROP TABLE](https://mariadb.com/docs/server/server-usage/tables/drop-table) followed by [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) instead of `TRUNCATE TABLE` may perform better. Setting [innodb\_adaptive\_hash\_index=OFF](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_adaptive_hash_index) can also help.

Setting [innodb\_adaptive\_hash\_index=OFF](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_adaptive_hash_index) can also improve `TRUNCATE TABLE` performance in general. See [MDEV-16796](https://jira.mariadb.org/browse/MDEV-16796) for more details.

## See Also

* [TRUNCATE function](https://mariadb.com/docs/server/reference/sql-functions/numeric-functions/truncate)
* [innodb\_safe\_truncate](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_safe_truncate) system variable
* [Oracle mode](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.