In this tutorial, we will learn about the
TRUNCATE statements in MySQL. Suppose we want to delete a table from the database altogether. Or, what if, we want to keep the table but delete all the records in the table? These are common operations in MySQL and for that, we use the
TRUNCATE commands respectively.
What are DROP and TRUNCATE statements?
TRUNCATE statement is used to delete the data and records inside the table, without deleting the table itself. The table structure remains preserved.
DROP statement is used to delete an existing table from a database. The DROP command deletes the table and all the records and information stored in it.
DROP are examples of Data Definition Language (DDL) commands.
MySQL TRUNCATE – Clear Rows From a MySQL Table
Let’s get right into the examples of the MySQL TRUNCATE statement here. In the below example, we’ll truncate the Students table that we’ve been using all along our tutorials here.
Syntax for MySQL TRUNCATE
TRUNCATE TABLE table_name;Code language: SQL (Structured Query Language) (sql)
Example of MySQL TRUNCATE
Consider the below Students table.
Suppose you want to clear all records from it. We do this using the
TRUNCATE TABLE Students;Code language: SQL (Structured Query Language) (sql)
Let us now use the
SELECT * FROM Students;Code language: SQL (Structured Query Language) (sql)
And let us also use the
DESC Students;Code language: SQL (Structured Query Language) (sql)
And we get the output as,
As you can see, when we use the
SELECT statement, we get an empty set as our output. Furthermore, when we use the
DESCRIBE command, we can see the structure of the table. What we can infer from this is that TRUNCATE clears all the records from the tables but does not delete the table. If you were to put an
INSERT statement after this to add a record, it would work.
MySQL DROP – Delete MySQL Tables and Databases
Let’s move to understanding the MySQL DROP command here. As we observed, the TRUNCATE command does not delete the table by itself. It only clears the data within the table. Let’s see how the DROP command reacts.
Syntax for MySQL DROP
DROP TABLE table_name;Code language: SQL (Structured Query Language) (sql)
Example of MySQL DROP
Now, what if we want to delete the Students table that we just truncated. We do so using the following query,
DROP TABLE Students;Code language: SQL (Structured Query Language) (sql)
And now, if we use the
DESCRIBE command, we get the following output –
The above error makes it clear that the DROP statement has deleted our Students table from the database.
In this article, we studied two of the basic DDL commands DROP and TRUNCATE. Deleting tables or clearing the data from them are common actions and for further reading, do check out the references.