MySQL DROP and TRUNCATE

In this tutorial, we will learn about the DROP and 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 DROP and TRUNCATE commands respectively.

What are DROP and TRUNCATE statements?

The TRUNCATE statement is used to delete the data and records inside the table, without deleting the table itself. The table structure remains preserved.

The 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.

Both, TRUNCATE and 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 query,

TRUNCATE TABLE Students;Code language: SQL (Structured Query Language) (sql)

Let us now use the SELECT query.

SELECT * FROM Students;Code language: SQL (Structured Query Language) (sql)

And let us also use the DESCRIBE command.

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.

Conclusion

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.

References

• MySQL official documentation on TRUNCATE.
• MySQL official documentation on DROP.
• JournalDev article on TRUNCATE.
• JournalDev article on DROP.