Truncate All Tables in MySQL

TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extent of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity-enforcing mechanisms. 

In SQL, truncate is used to delete all data from the table but doesn’t delete the table structure. However, it drops all the rows in one go, once executed.

Syntax: 

TRUNCATE TABLE TABLE_NAME;

Truncate is useful in places where we need to remove all the records of the table. It saves time and is usually faster. However, unlike the Drop statement which deletes both the data and structure, truncate just deletes the data and preserves the table structure that can be utilized later.

Steps for Truncate All Tables in Mysql:

Step 1: Create a database called GeeksforGeeks using the following query:

Query:

CREATE DATABASE GeeksForGeeks;

Step 2:  Now we will use that database using the given command:

Query:

USE GeeksForGeeks

Step 3: Now we will create a table called student having 4 columns, 1) id 2) roll_no 3) name 4) city 

Query:

CREATE TABLE STUDENT (ID VARCHAR2(20), 
ROLL_NO INT, NAME VARCHAR2(50),
CITY VARCHAR2(50));

Step 4: To insert 4 rows in the table, execute the following query:

Query: 

INSERT INTO STUDENT VALUES ( 'S111', 1, 'RAHUL', 'DELHI');
INSERT INTO STUDENT VALUES ( 'S121', 2, 'RAJ', 'MUMBAI');
INSERT INTO STUDENT VALUES ( 'S131', 3, 'JOE', 'BANGALORE');
INSERT INTO STUDENT VALUES ( 'S141', 4, 'RUTU', 'KOLKATA');

 Step 5: To display all the values of a table, execute select:

Query:

SELECT * FROM STUDENT;

Step 6:  To flush all the data from a table, we will use the truncate command:

Query: 

TRUNCATE TABLE STUDENT;

Step 7: After truncating we will check what’s inside our table by using a select statement:

Query: 

SELECT * FROM STUDENT;

Step 8: All the rows (data) have been deleted in a single go. But the table structure prevails and that can be checked using following command:

Query: 

exec sp_columns STUDENT;

Output: 

That is the basic purpose of the truncate command. It will keep the schema as it is for later use and simply clears all the values of a table, which is one of the reasons for it being faster. But the disadvantage is that we cannot roll back once a truncate is executed. So the table contents are not retrieved.