Truncate table in MySQL

Home - Tutorials - MySQL

This article demonstrates how to use the TRUNCATE command and what is the difference between truncate and delete in MySQL.

Tutorial info:

Name:	Truncate table in MySQL
Total steps:	1
Category:	MySQL
Date:	2010-11-10
Level:	Beginner
Product:	See complete product
Viewed:	8294

Bookmark Truncate table in MySQL

Step 1 - Truncate table in MySQL

Truncate table in MySQL

To remove all records from a MySQL database table you can use the delete or the truncate command. Despite of the same result there are important differences between the commands.

The syntax of truncate is the following:

Code:

```
TRUNCATE TABLE myTable;
```

Database F1

As result all records will be removed from myTable and auto_increment value will be reseted as well.

The main benefit of using truncate instead of delete is that truncate is much faster. However you have to pay for this performance. The truncate command doesn’t simply remove the records from the table, but drops and recreates the table. This also means that you can not remove only a subset of the records and also means that it is not possible to make a rollback. So be careful!

Besides this the truncate command doesn’t return with the number of the deleted records so keep it in mind if you need this information.

Here is a simple workflow how to demonstrate the truncate command:

1. Create a table with an auto_increment field

Code:

```
CREATE TABLE `user` (
```
```
  `id` INT(11) NOT NULL AUTO_INCREMENT,
```
```
  `name` VARCHAR(100) DEFAULT NULL,
```
```
  PRIMARY KEY (`id`)
```
```
)
```
```
 
```

Database F1

2. Fill it with data

Code:

```
INSERT INTO USER (NAME) VALUES ('Bob');
```

INSERT INTO USER (NAME) VALUES ('Mark');

INSERT INTO USER (NAME) VALUES ('Alex');

INSERT INTO USER (NAME) VALUES ('Julia');

Database F1

3. List table data with select and check the id values

Code:

```
SELECT * FROM USER;
```
```
 
```
```
The result is:
```

id         name

1          Bob

2          Mark

3          Alex

```
4          Julia  
```

Database F1

4. Truncate the table

Code:

```
TRUNCATE TABLE USER;
```

Database F1

5. Add data again

Code:

INSERT INTO USER (NAME) VALUES ('Bill');

Database F1

6. Check the id fields

Code:

```
SELECT * FROM USER;
```
```
 
```
```
The result is:
```

id         name

```
1          Bill
```

Database F1

As you can see the old records were removed and the new one gets 1 for the id field.

Tags: mysql truncate table, truncate table, remove all records, truncate, delete, remove

Truncate table in MySQL - Table of contents
Step 1 - Truncate table in MySQL

Database F1

Help in Database, get Database code, scripts, tutorials

Truncate table in MySQL

Tutorial info:

Bookmark Truncate table in MySQL

Step 1 - Truncate table in MySQL