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:10793

Bookmark Truncate table in MySQL



AddThis Social Bookmark Button

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:
  1. TRUNCATE TABLE myTable;

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:
  1. CREATE TABLE `user` (
  2.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `name` VARCHAR(100) DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. )
  6.  

2. Fill it with data

Code:
  1. INSERT INTO USER (NAME) VALUES ('Bob');
  2. INSERT INTO USER (NAME) VALUES ('Mark');
  3. INSERT INTO USER (NAME) VALUES ('Alex');
  4. INSERT INTO USER (NAME) VALUES ('Julia');

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

Code:
  1. SELECT * FROM USER;
  2.  
  3. The result is:
  4. id         name                                                                                                                                                                                                                                                                                                        
  5. 1          Bob                                                                                                                                                                                                                                                                                                         
  6. 2          Mark                                                                                                                                                                                                                                                                                                        
  7. 3          Alex                                                                                                                                                                                                                                                                                                        
  8. 4          Julia  

4. Truncate the table

Code:
  1. TRUNCATE TABLE USER;

5. Add data again

Code:
  1. INSERT INTO USER (NAME) VALUES ('Bill');

6. Check the id fields

Code:
  1. SELECT * FROM USER;
  2.  
  3. The result is:
  4. id         name                                                                                                                                                                                                                                                                                                        
  5. 1          Bill

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



F1 Site Family
AJAX F1
CSS F1
Database F1
Flash F1
HTML F1
Java F1
JavaScript F1
PhotoShop F1
PHP F1
Scripts F1
Tutorial F1
Windows F1

Family tutorials
PHP Array
PHP Redirect
PHP Session
JavaScript String
JavaScript Timeout
JavaScript Tooltip
AJAX PHP
AJAX File Upload
AJAX Rating System

Total time: 0.03