# **TRUNCATE TABLE**

## **Grammar Description**

The `TRUNCATE TABLE` statement is used to delete all rows in a table without recording a single row deletion operation. `TRUNCATE TABLE` is similar to the DELETE statement without the `WHERE` clause; however, `TRUNCATE TABLE` is faster and uses fewer system and transaction log resources.

`TRUNCATE TABLE` has the following characteristics:

- `TRUNCATE TABLE` After deletion, it cannot be restored.

- If the table has the `AUTO_INCREMENT` column, the `TRUNCATE TABLE` statement resets the auto-increment value to zero.

- If the table has any foreign key constraints (`FOREIGN KEY`), the `TRUNCATE TABLE` statement deletes rows one by one.

- If the table does not have any foreign key constraints (`FOREIGN KEY`), the `TRUNCATE TABLE` statement will delete the table and recreate a new table with the same structure

The difference between `DROP TABLE`, `TRUNCATE TABLE` and `DELETE TABLE`:

- `DROP TABLE`: Use `DROP TABLE` when you no longer need the table.
- `TRUNCATE TABLE`: Use `TRUNCATE TABLE` when you want to delete all records but still keep the table.
- `DELETE TABLE`: When you want to delete a partial record, use `DELETE TABLE`.

## **Grammar Structure**

```sql
TRUNCATE [TABLE] table_name;
```

### Syntax interpretation

#### TABLE

The TABLE keyword is optional. Use it to distinguish the `TRUNCATE TABLE` statement from the `TRUNCATE` function.

## **Example**

```text
create table index_table_05 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key col2(col2),key num_id(col4));
insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12');
mysql> select * from index_table_05;
+------+------+-----+
| col1 | col2 | col3 | col4 |
+------+------+-----+
| 1 | apple | 1 | 10 |
| 2 | store | 2 | 11 |
| 3 | bread | 3 | 12 |
+------+------+-----+
3 rows in set (0.00 sec)

mysql> truncate table index_table_05;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from index_table_05;
Empty set (0.03 sec)
```